[转载]SQLServer行转列问题-pivot使用详解

[转载]老掉牙的行转列问题-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的顺序无关

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏