sql中 行转列 (一)

近一段时间一直没怎么看过sql了，突袭一下：

行转列，列转行是我们在开发过程中经常碰到的问题。行转列一般通过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( CASE Subject WHEN'语文'THEN Score ELSE 0 END)AS '语文',
MAX(CASE Subject WHEN'数学'THEN Score ELSE 0 END)AS'数学',
MAX(CASE Subject WHEN'英语'THEN Score ELSE 0 END)AS'英语',
MAX(CASE Subject WHEN'生物'THEN Score ELSE 0 END)AS'生物' from
[StudentScores]
GROUP BY UserName




有一个游戏玩家充值表（仅仅为了说明，举的一个小例子），

代码

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


统计数据的需求，要求按日期、支付方式来统计充值金额信息，脚本如下：



SELECT
CreateTime,
ISNULL(SUM([支付宝]),0)AS[支付宝],
ISNULL(SUM([手机短信]),0)AS[手机短信],
ISNULL(SUM([工商银行卡]),0)AS[工商银行卡],
ISNULL(SUM([建设银行卡]),0)AS[建设银行卡]
FROM
(
SELECT CONVERT(VARCHAR(10),CreateTime,120)AS CreateTime,
CASE PayType WHEN'支付宝'THEN SUM(Money)ELSE 0 END AS'支付宝',
CASE PayType WHEN'手机短信'THEN SUM(Money)ELSE 0 END AS'手机短信',
CASE PayType WHEN'工商银行卡'THEN SUM(Money)ELSE 0 END AS'工商银行卡',
CASE PayType WHEN'建设银行卡'THEN SUM(Money)ELSE 0 END AS'建设银行卡'
FROM Inpours
GROUP BY CreateTime,PayType
)T
GROUP BY CreateTime

其实行转列，关键是要理清逻辑，而且对分组（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 是所在数据库的名称。