老掉牙的行转列问题-pivot使用详解

一背景设定

行转列的典型应用场景，比如报表，交叉表。还有一个就是：面试。。。

行转列是对单行值的拆分，拆分的途径比如利用聚合函数。拆分之后变成多列。

借用网上通用的样例，对，就是小王小明四门功课。有印象了吧。

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的顺序无关。