SQL 中的行转列和列转行

光的旅行 發表於 2023-8-7 00:00:00

行转列，列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 的运算符PIVOT来实现。用传统的方法，比较好理解。层次清晰，而且比较习惯。但是PIVOT 、UNPIVOT提供的语法比一系列复杂的SELECT…CASE 语句中所指定的语法更简单、更具可读性。下面我们通过几个简单的例子来介绍一下列转行、行转列问题。


<img title="SQL 中的行转列和列转行" alt="SQL 中的行转列和列转行" border="0" src="https://zhuji.jb51.net/uploads/img/202305/300b7bf9d0fc97206872a07129d1707f.jpg">

我们首先先通过一个老生常谈的例子，学生成绩表(下面简化了些)来形象了解下行转列

<ol class="dp-sql">
<li class="alt">
CREATETABLE
</li>
<li>
(
</li>
<li class="alt">
NVARCHAR(20),--学生姓名
</li>
<li>
NVARCHAR(30),--科目
</li>
<li class="alt">
FLOAT,--成绩
</li>
<li>
)
</li>
<li class="alt">

</li>
<li>
INSERTINTOSELECT'Nick','语文',80
</li>
<li class="alt">
INSERTINTOSELECT'Nick','数学',90
</li>
<li>
INSERTINTOSELECT'Nick','英语',70
</li>
<li class="alt">
INSERTINTOSELECT'Nick','生物',85
</li>
<li>
INSERTINTOSELECT'Kent','语文',80
</li>
<li class="alt">
INSERTINTOSELECT'Kent','数学',90
</li>
<li>
INSERTINTOSELECT'Kent','英语',70
</li>
<li class="alt">
INSERTINTOSELECT'Kent','生物',85
</li>
</ol>

如果我想知道每位学生的每科成绩，而且每个学生的全部成绩排成一行，这样方便我查看、统计，导出数据

<ol class="dp-sql">
<li class="alt">
SELECT
</li>
<li>
UserName,
</li>
<li class="alt">
MAX(CASESubjectWHEN'语文'THENScoreELSE0END)AS'语文',
</li>
<li>
MAX(CASESubjectWHEN'数学'THENScoreELSE0END)AS'数学',
</li>
<li class="alt">
MAX(CASESubjectWHEN'英语'THENScoreELSE0END)AS'英语',
</li>
<li>
MAX(CASESubjectWHEN'生物'THENScoreELSE0END)AS'生物'
</li>
<li class="alt">
FROMdbo.
</li>
<li>
GROUPBYUserName
</li>
</ol>

查询结果如图所示，这样我们就能很清楚的了解每位学生所有的成绩了


<img title="SQL 中的行转列和列转行" alt="SQL 中的行转列和列转行" border="0" src="https://zhuji.jb51.net/uploads/img/202305/52c8934a93cd822e7dc68aa16e632345.jpg">

接下来我们来看看第二个小列子。有一个游戏玩家充值表(仅仅为了说明，举的一个小例子)，

<ol class="dp-sql">
<li class="alt">
CREATETABLE
</li>
<li>
(
</li>
<li class="alt">
INTIDENTITY(1,1),
</li>
<li>
NVARCHAR(20),--游戏玩家
</li>
<li class="alt">
DATETIME,--充值时间
</li>
<li>
NVARCHAR(20),--充值类型
</li>
<li class="alt">
DECIMAL,--充值金额
</li>
<li>
BIT,--是否成功1表示成功，0表示失败
</li>
<li class="alt">
CONSTRAINTPRIMARYKEY(ID)
</li>
<li>
)
</li>
<li class="alt">
INSERTINTOInpoursSELECT'张三','2010-05-01','支付宝',50,1
</li>
<li>
INSERTINTOInpoursSELECT'张三','2010-06-14','支付宝',50,1
</li>
<li class="alt">
INSERTINTOInpoursSELECT'张三','2010-06-14','手机短信',100,1
</li>
<li>
INSERTINTOInpoursSELECT'李四','2010-06-14','手机短信',100,1
</li>
<li class="alt">
INSERTINTOInpoursSELECT'李四','2010-07-14','支付宝',100,1
</li>
<li>
INSERTINTOInpoursSELECT'王五','2010-07-14','工商银行卡',100,1
</li>
<li class="alt">
INSERTINTOInpoursSELECT'赵六','2010-07-14','建设银行卡',100,1
</li>
</ol>

下面来了一个统计数据的需求，要求按日期、支付方式来统计充值金额信息。这也是一个典型的行转列的例子。我们可以通过下面的脚本来达到目的

<ol class="dp-sql">
<li class="alt">
SELECT
</li>
<li>
CONVERT(VARCHAR(10),CreateTime,120)ASCreateTime,
</li>
<li class="alt">
CASEPayTypeWHEN'支付宝'THENSUM(Money)ELSE0ENDAS'支付宝',
</li>
<li>
CASEPayTypeWHEN'手机短信'THENSUM(Money)ELSE0ENDAS'手机短信',
</li>
<li class="alt">
CASEPayTypeWHEN'工商银行卡'THENSUM(Money)ELSE0ENDAS'工商银行卡',
</li>
<li>
CASEPayTypeWHEN'建设银行卡'THENSUM(Money)ELSE0ENDAS'建设银行卡'
</li>
<li class="alt">
FROMInpours
</li>
<li>
GROUPBYCreateTime,PayType
</li>
</ol>

如图所示，我们这样只是得到了这样的输出结果，还需进一步处理，才能得到想要的结果


<img title="SQL 中的行转列和列转行" alt="SQL 中的行转列和列转行" border="0" src="https://zhuji.jb51.net/uploads/img/202305/64740c45b130cc4555a944338424cdc8.jpg">
<ol class="dp-sql">
<li class="alt">
SELECT
</li>
<li>
CreateTime,
</li>
<li class="alt">
ISNULL(SUM([支付宝]),0)AS[支付宝],
</li>
<li>
ISNULL(SUM([手机短信]),0)AS[手机短信],
</li>
<li class="alt">
ISNULL(SUM([工商银行卡]),0)AS[工商银行卡],
</li>
<li>
ISNULL(SUM([建设银行卡]),0)AS[建设银行卡]
</li>
<li class="alt">
FROM
</li>
<li>
(
</li>
<li class="alt">
SELECT
</li>
<li>
CONVERT(VARCHAR(10),CreateTime,120)ASCreateTime,
</li>
<li class="alt">
CASEPayTypeWHEN'支付宝'THENSUM(Money)ELSE0ENDAS'支付宝',
</li>
<li>
CASEPayTypeWHEN'手机短信'THENSUM(Money)ELSE0ENDAS'手机短信',
</li>
<li class="alt">
CASEPayTypeWHEN'工商银行卡'THENSUM(Money)ELSE0ENDAS'工商银行卡',
</li>
<li>
CASEPayTypeWHEN'建设银行卡'THENSUM(Money)ELSE0ENDAS'建设银行卡'
</li>
<li class="alt">
FROMInpours
</li>
<li>
GROUPBYCreateTime,PayType
</li>
<li class="alt">
)T
</li>
<li>
GROUPBYCreateTime
</li>
</ol>

其实行转列，关键是要理清逻辑，而且对分组(Group by)概念比较清晰。上面两个列子基本上就是行转列的类型了。但是有个问题来了，上面是我为了说明弄的一个简单列子。实际中，可能支付方式特别多，而且逻辑也复杂很多，可能涉及汇率、手续费等等(曾经做个这样一个)，如果支付方式特别多，我们的CASE WHEN 会弄出一大堆，确实比较恼火，而且新增一种支付方式，我们还得修改脚本如果把上面的脚本用动态SQL改写一下，我们就能轻松解决这个问题

<ol class="dp-sql">
<li class="alt">
DECLARE@cmdTextVARCHAR(8000);
</li>
<li>
DECLARE@tmpSqlVARCHAR(8000);
</li>
<li class="alt">
SET@cmdText='SELECTCONVERT(VARCHAR(10),CreateTime,120)ASCreateTime,'+CHAR(10);
</li>
<li>
SELECT@cmdText=@cmdText+'CASEPayTypeWHEN'''+
</li>
<li class="alt">
PayType+'''THENSUM(Money)ELSE0ENDAS'''+PayType
</li>
<li>
+''','+CHAR(10)FROM(SELECTDISTINCTPayTypeFROMInpours)T
</li>
<li class="alt">

</li>
<li>
SET@cmdText=LEFT(@cmdText,LEN(@cmdText)-2)--注意这里，如果没有加CHAR(10)则用LEFT(@cmdText,LEN(@cmdText)-1)
</li>
<li class="alt">

</li>
<li>
SET@cmdText=@cmdText+'FROMInpours
</li>
<li class="alt">
GROUPBYCreateTime,PayType';
</li>
<li>

</li>
<li class="alt">
SET@tmpSql='SELECTCreateTime,'+CHAR(10);
</li>
<li>
SELECT@tmpSql=@tmpSql+'ISNULL(SUM('+PayType+'),0)AS'''+
</li>
<li class="alt">
PayType+''','+CHAR(10)
</li>
<li>
FROM(SELECTDISTINCTPayTypeFROMInpours)T
</li>
<li class="alt">

</li>
<li>
SET@tmpSql=LEFT(@tmpSql,LEN(@tmpSql)-2)+'FROM('+CHAR(10);
</li>
<li class="alt">

</li>
<li>
SET@cmdText=@tmpSql+@cmdText+')TGROUPBYCreateTime';
</li>
<li class="alt">
PRINT@cmdText
</li>
<li>
EXECUTE(@cmdText);
</li>
</ol>

下面是通过PIVOT来进行行转列的用法，大家可以对比一下，确实要简单、更具可读性

<ol class="dp-sql">
<li class="alt">
SELECTCreateTime,[支付宝],[手机短信],[工商银行卡],[建设银行卡]
</li>
<li>
FROM
</li>
<li class="alt">
(
</li>
<li>
SELECTCONVERT(VARCHAR(10),CreateTime,120)ASCreateTime,PayType,Money
</li>
<li class="alt">
FROMInpours
</li>
<li>
)P
</li>
<li class="alt">
PIVOT(
</li>
<li>
SUM(Money)
</li>
<li class="alt">
FORPayTypeIN
</li>
<li>
([支付宝],[手机短信],[工商银行卡],[建设银行卡])
</li>
<li class="alt">
)AST
</li>
<li>
ORDERBYCreateTime
</li>
</ol>

有时可能会出现这样的错误：


消息 325，级别 15，状态 1，第 9 行


‘PIVOT’ 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。


这个是因为：对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。有关如何设置数据库兼容级别的信息，请参阅 sp_dbcmptlevel (Transact-SQL)。例如，只需在执行上面脚本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了， Test 是所在数据库的名称。


下面我们来看看列转行，主要是通过UNION ALL ,MAX来实现。假如有下面这么一个表

<ol class="dp-sql">
<li class="alt">
CreateTableProgrectDetail
</li>
<li>
(
</li>
<li class="alt">
ProgrectNameNVARCHAR(20),--工程名称
</li>
<li>
OverseaSupplyINT,--海外供应商供给数量
</li>
<li class="alt">
NativeSupplyINT,--国内供应商供给数量
</li>
<li>
SouthSupplyINT,--南方供应商供给数量
</li>
<li class="alt">
NorthSupplyINT--北方供应商供给数量
</li>
<li>
)
</li>
<li class="alt">

</li>
<li>
INSERTINTOProgrectDetail
</li>
<li class="alt">
SELECT'A',100,200,50,50
</li>
<li>
UNIONALL
</li>
<li class="alt">
SELECT'B',200,300,150,150
</li>
<li>
UNIONALL
</li>
<li class="alt">
SELECT'C',159,400,20,320
</li>
<li>
UNIONALL
</li>
<li class="alt">
SELECT'D',250,30,15,15
</li>
</ol>

我们可以通过下面的脚本来实现，查询结果如下图所示

<ol class="dp-sql">
<li class="alt">
SELECTProgrectName,'OverseaSupply'ASSupplier,
</li>
<li>
MAX(OverseaSupply)AS'SupplyNum'
</li>
<li class="alt">
FROMProgrectDetail
</li>
<li>
GROUPBYProgrectName
</li>
<li class="alt">
UNIONALL
</li>
<li>
SELECTProgrectName,'NativeSupply'ASSupplier,
</li>
<li class="alt">
MAX(NativeSupply)AS'SupplyNum'
</li>
<li>
FROMProgrectDetail
</li>
<li class="alt">
GROUPBYProgrectName
</li>
<li>
UNIONALL
</li>
<li class="alt">
SELECTProgrectName,'SouthSupply'ASSupplier,
</li>
<li>
MAX(SouthSupply)AS'SupplyNum'
</li>
<li class="alt">
FROMProgrectDetail
</li>
<li>
GROUPBYProgrectName
</li>
<li class="alt">
UNIONALL
</li>
<li>
SELECTProgrectName,'NorthSupply'ASSupplier,
</li>
<li class="alt">
MAX(NorthSupply)AS'SupplyNum'
</li>
<li>
FROMProgrectDetail
</li>
<li class="alt">
GROUPBYProgrectName
</li>
</ol>

<img title="SQL 中的行转列和列转行" alt="SQL 中的行转列和列转行" border="0" src="https://zhuji.jb51.net/uploads/img/202305/6a2bd773e3e9128d7b87af2176ed367c.jpg">

用UNPIVOT 实现如下：

<ol class="dp-sql">
<li class="alt">
SELECTProgrectName,Supplier,SupplyNum
</li>
<li>
FROM
</li>
<li class="alt">
(
</li>
<li>
SELECTProgrectName,OverseaSupply,NativeSupply,
</li>
<li class="alt">
SouthSupply,NorthSupply
</li>
<li>
FROMProgrectDetail
</li>
<li class="alt">
)T
</li>
<li>
UNPIVOT
</li>
<li class="alt">
(
</li>
<li>
SupplyNumFORSupplierIN
</li>
<li class="alt">
(OverseaSupply,NativeSupply,SouthSupply,NorthSupply)
</li>
<li>
)P
</li>
</ol>

原文链接：https://mp.weixin.qq.com/s?__biz=MzA3MTg4NjY4Mw==&mid=2457323770&idx=2&sn=d3209bed52a39106bbe452a9a5b35b3c&chksm=88a5dacebfd253d8acaf8278b10e3b8331ed12b4fcb41eaab6a6d05cae82869ad56111e1a6b2&mpshare=1&

頁: [1]

琼殿技术论坛's Archiver

SQL 中的行转列和列转行