[转载]老掉牙的行转列问题-pivot使用详解 – 屠龙解牛 – 博客园.
一背景设定
行转列的典型应用场景,比如报表,交叉表。还有一个就是:面试。。。
行转列是对单行值的拆分,拆分的途径比如利用聚合函数。拆分之后变成多列。
借用网上通用的样例,对,就是小王小明四门功课。有印象了吧。
data
CREATE TABLE [StudentScores] ( [UserName] NVARCHAR(20), --学生姓名 [Subject] NVARCHAR(30), --科目 [Score] FLOAT, --成绩 ) INSERT INTO [StudentScores] SELECT 'Nick', '语文', 80 INSERT INTO [StudentScores] SELECT 'Nick', '数学', 90 INSERT INTO [StudentScores] SELECT 'Nick', '英语', 70 INSERT INTO [StudentScores] SELECT 'Nick', '生物', 85 INSERT INTO [StudentScores] SELECT 'Kent', '语文', 80 INSERT INTO [StudentScores] SELECT 'Kent', '数学', 90 INSERT INTO [StudentScores] SELECT 'Kent', '英语', 70 INSERT INTO [StudentScores] SELECT 'Kent', '生物', 85
在SQLServer2005出现之前,一般的做法是用case when.
如下所示
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 dbo.[StudentScores] GROUP BY UserName
——————-以上可忽略不看——————-
二 PIVOT:how?
SQL2005推出了新的函数PIVOT.
pivot这个单词本身是旋转的意思,行转列本质上也一样,从魔方的这个面到另一个。
使用pivot做好 5 steps.
1从返回的字段名开始
2接下来跟上 子查询
3指定聚合列
4for指定要转化的行值
5对整个查询别名
SELECT username,语文,数学,英语,生物 --step1 FROM ( SELECT UserName,[Subject],Score FROM StudentScores ) AS a --step2 PIVOT ( MAX(score) --step3 FOR [Subject] IN ([语文],[英语],[生物],[数学]) --step4 ) AS b ORDER BY UserName --step5
ps:几点细节:step1中字符串不能加引号;step4中in后的列表只能加中括号,不能加引号;1和4的引用数目要一致,但和4的顺序无关