行转列，列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。 用传统的方法，比较好理解。层次清晰，而且比较习惯。 但是PIVOT 、UNPIVOT提供的语法比一系列复杂的 SELECT…CASE 语句中所指定的语法更简单、更具可读性。下面我们通过几个简单的例子来介绍一下列转行、行转列问题。
我们首先先通过一个老生常谈的例子，学生成绩表(下面简化了些)来形象了解下行转列
CREATETABLE[StudentScores] ( [UserName]NVARCHAR(20),--学生姓名 [Subject]NVARCHAR(30),--科目 [Score]FLOAT,--成绩 ) INSERTINTO[StudentScores]SELECT'Nick','语文',80 INSERTINTO[StudentScores]SELECT'Nick','数学',90 INSERTINTO[StudentScores]SELECT'Nick','英语',70 INSERTINTO[StudentScores]SELECT'Nick','生物',85 INSERTINTO[StudentScores]SELECT'Kent','语文',80 INSERTINTO[StudentScores]SELECT'Kent','数学',90 INSERTINTO[StudentScores]SELECT'Kent','英语',70 INSERTINTO[StudentScores]SELECT'Kent','生物',85
如果我想知道每位学生的每科成绩，而且每个学生的全部成绩排成一行，这样方便我查看、统计，导出数据
SELECT UserName, MAX(CASESubjectWHEN'语文'THENScoreELSE0END)AS'语文', MAX(CASESubjectWHEN'数学'THENScoreELSE0END)AS'数学', MAX(CASESubjectWHEN'英语'THENScoreELSE0END)AS'英语', MAX(CASESubjectWHEN'生物'THENScoreELSE0END)AS'生物'FROMdbo.[StudentScores] GROUPBYUserName
查询结果如图所示，这样我们就能很清楚的了解每位学生所有的成绩了
接下来我们来看看第二个小列子。有一个游戏玩家充值表（仅仅为了说明，举的一个小例子），
CREATETABLE[Inpours] ( [ID]INTIDENTITY(1,1), [UserName]NVARCHAR(20),--游戏玩家 [CreateTime]DATETIME,--充值时间 [PayType]NVARCHAR(20),--充值类型 [Money]DECIMAL,--充值金额 [IsSuccess]BIT,--是否成功1表示成功，0表示失败 CONSTRAINT[PK_Inpours_ID]PRIMARYKEY(ID) ) INSERTINTOInpoursSELECT'张三','2010-05-01','支付宝',50,1 INSERTINTOInpoursSELECT'张三','2010-06-14','支付宝',50,1 INSERTINTOInpoursSELECT'张三','2010-06-14','手机短信',100,1 INSERTINTOInpoursSELECT'李四','2010-06-14','手机短信',100,1 INSERTINTOInpoursSELECT'李四','2010-07-14','支付宝',100,1 INSERTINTOInpoursSELECT'王五','2010-07-14','工商银行卡',100,1 INSERTINTOInpoursSELECT'赵六','2010-07-14','建设银行卡',100,1
下面来了一个统计数据的需求，要求按日期、支付方式来统计充值金额信息。这也是一个典型的行转列的例子。我们可以通过下面的脚本来达到目的
SELECTCONVERT(VARCHAR(10),CreateTime,120)ASCreateTime, CASEPayTypeWHEN'支付宝'THENSUM(Money)ELSE0ENDAS'支付宝', CASEPayTypeWHEN'手机短信'THENSUM(Money)ELSE0ENDAS'手机短信', CASEPayTypeWHEN'工商银行卡'THENSUM(Money)ELSE0ENDAS'工商银行卡', CASEPayTypeWHEN'建设银行卡'THENSUM(Money)ELSE0ENDAS'建设银行卡'FROMInpours GROUPBYCreateTime,PayType
如图所示，我们这样只是得到了这样的输出结果，还需进一步处理，才能得到想要的结果
SELECT CreateTime, ISNULL(SUM([支付宝]),0)AS[支付宝], ISNULL(SUM([手机短信]),0)AS[手机短信], ISNULL(SUM([工商银行卡]),0)AS[工商银行卡], ISNULL(SUM([建设银行卡]),0)AS[建设银行卡] FROM( SELECTCONVERT(VARCHAR(10),CreateTime,120)ASCreateTime, CASEPayTypeWHEN'支付宝'THENSUM(Money)ELSE0ENDAS'支付宝', CASEPayTypeWHEN'手机短信'THENSUM(Money)ELSE0ENDAS'手机短信', CASEPayTypeWHEN'工商银行卡'THENSUM(Money)ELSE0ENDAS'工商银行卡', CASEPayTypeWHEN'建设银行卡'THENSUM(Money)ELSE0ENDAS'建设银行卡'FROMInpours GROUPBYCreateTime,PayType )T GROUPBYCreateTime
其实行转列，关键是要理清逻辑，而且对分组（Group by）概念比较清晰。上面两个列子基本上就是行转列的类型了。但是有个问题来了，上面是我为了说明弄的一个简单列子。实际中，可能支付方式特别多，而且逻辑也复杂很多，可能涉及汇率、手续费等等(曾经做个这样一个)，如果支付方式特别多，我们的CASE WHEN 会弄出一大堆，确实比较恼火，而且新增一种支付方式，我们还得修改脚本如果把上面的脚本用动态SQL改写一下，我们就能轻松解决这个问题
DECLARE@cmdTextVARCHAR(8000); DECLARE@tmpSqlVARCHAR(8000); SET@cmdText='SELECTCONVERT(VARCHAR(10),CreateTime,120)ASCreateTime,'+CHAR(10); SELECT@cmdText=@cmdText+'CASEPayTypeWHEN'''+PayType+'''THENSUM(Money)ELSE0ENDAS'''+PayType +''','+CHAR(10)FROM(SELECTDISTINCTPayTypeFROMInpours)T SET@cmdText=LEFT(@cmdText,LEN(@cmdText)-2)--注意这里，如果没有加CHAR(10)则用LEFT(@cmdText,LEN(@cmdText)-1) SET@cmdText=@cmdText+'FROMInpoursGROUPBYCreateTime,PayType'; SET@tmpSql='SELECTCreateTime,'+CHAR(10); SELECT@tmpSql=@tmpSql+'ISNULL(SUM('+PayType+'),0)AS'''+PayType+''','+CHAR(10) FROM(SELECTDISTINCTPayTypeFROMInpours)T SET@tmpSql=LEFT(@tmpSql,LEN(@tmpSql)-2)+'FROM('+CHAR(10); SET@cmdText=@tmpSql+@cmdText+')TGROUPBYCreateTime'; PRINT@cmdText EXECUTE(@cmdText);
下面是通过PIVOT来进行行转列的用法，大家可以对比一下，确实要简单、更具可读性（呵呵，习惯的前提下）
SELECT CreateTime,[支付宝],[手机短信], [工商银行卡],[建设银行卡] FROM( SELECTCONVERT(VARCHAR(10),CreateTime,120)ASCreateTime,PayType,Money FROMInpours )P PIVOT( SUM(Money) FORPayTypeIN([支付宝],[手机短信],[工商银行卡],[建设银行卡]) )AST ORDERBYCreateTime
有时可能会出现这样的错误：
消息 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来实现。假如有下面这么一个表
CREATETABLEProgrectDetail ( ProgrectNameNVARCHAR(20),--工程名称 OverseaSupplyINT,--海外供应商供给数量 NativeSupplyINT,--国内供应商供给数量 SouthSupplyINT,--南方供应商供给数量 NorthSupplyINT--北方供应商供给数量 ) INSERTINTOProgrectDetail SELECT'A',100,200,50,50 UNIONALLSELECT'B',200,300,150,150 UNIONALLSELECT'C',159,400,20,320 UNIONALLSELECT'D',250,30,15,15
我们可以通过下面的脚本来实现，查询结果如下图所示
SELECTProgrectName,'OverseaSupply'ASSupplier, MAX(OverseaSupply)AS'SupplyNum'FROMProgrectDetail GROUPBYProgrectName UNIONALLSELECTProgrectName,'NativeSupply'ASSupplier, MAX(NativeSupply)AS'SupplyNum'FROMProgrectDetail GROUPBYProgrectName UNIONALLSELECTProgrectName,'SouthSupply'ASSupplier, MAX(SouthSupply)AS'SupplyNum'FROMProgrectDetail GROUPBYProgrectName UNIONALLSELECTProgrectName,'NorthSupply'ASSupplier, MAX(NorthSupply)AS'SupplyNum'FROMProgrectDetail GROUPBYProgrectName
用UNPIVOT 实现如下：
SELECTProgrectName,Supplier,SupplyNum FROM ( SELECTProgrectName,OverseaSupply,NativeSupply, SouthSupply,NorthSupply FROMProgrectDetail )T UNPIVOT ( SupplyNumFORSupplierIN(OverseaSupply,NativeSupply,SouthSupply,NorthSupply) )P
原文标题:重温SQL——行转列，列转行
链接：http://www.cnblogs.com/kerrycode/archive/2010/07/28/1786547.html
【编辑推荐】
- SQL Server数据库和Oracle行转列的特殊方案描述SQL Server行转列的什么情况下被用？SQL Server实例中对另个实例的调用SQL Server identity列，美中不足之处SQL Server DateTime数据类型的另类解读