5分钟学会SQL SERVER PIVOT操作

fireWang

发布于 2020-03-25 17:49:23

8K0

发布于 2020-03-25 17:49:23

PIVOT和UNPIVOT

PIVOT 通过将表达式中的一个列的唯一值转换为输出中的多列（即行转列），来轮替表值表达式。PIVOT 在需要对最终输出所需的所有剩余列值执行聚合时运行聚合。与 PIVOT 执行的操作相反，UNPIVOT 将表值表达式的列轮换为行（即列转行）。

但是需要注意得是，UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 执行聚合，并将多个可能的行合并为输出中的一行。UNPIVOT 不重现原始表值表达式的结果，因为行已被合并。

-- PIVOT 语法
SELECT <非透视的列>,
    [第一个透视的列] AS <列名称>,
    [第二个透视的列] AS <列名称>,
    ...
    [最后一个透视的列] AS <列名称>,

FROM
    (<生成数据的 SELECT 查询>) 
    AS <源查询的别名>

PIVOT
(
    <聚合函数>(<要聚合的列>)
FOR

[<包含要成为列标题的值的列>]
    IN ( [第一个透视的列], [第二个透视的列],
    ... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;

实例的数据使用的是和 SQL笔试50题同样的数据，使用的平台是SQLFIDDLE（提供在线数据库），鉴于近期全球病情的影响，各种网站都有一定几率无法提供服务，本次提供了SQLite数据库，已存入测试数据。

下载地址（14天内有效）：https://c-t.work/s/1786d12bba3e4c

-- 查看每个人的年龄，性别，三门课成绩
select
sid,sname,sage,ssex,[语文],[数学],[英语]
from
(
select a.sid,a.sname,a.sage,a.ssex,c.cname,b.score
  from Student a
  left join Score b 
  on a.sid=b.sid
  left join Course c
  on b.cid = c.cid
) source_table
pivot(
  sum(score) for
cname in (
  [语文],[数学],[英语]
)
     ) t

student_pivot

将上述结果新建表 Student_pivot

create table Student_pivot (
sid varchar(10),sname nvarchar(10),sage datetime,ssex nvarchar(10), "语文" int, "数学" int,"英语" int);
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男', 80, 90, 99);
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男', 70, 60, 80);
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男', 80, 80, 80);
insert into Student values('04' , N'李云' , '1990-08-06' , N'男', 50, 30, 20);
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女', 76, 87, null);
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女', 31, null, 34);
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女', null, 89, 98);
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女', null,null,null);

-- unpivot 语法
SELECT [columns not unpivoted],
     [unpivot_column],
       [value_column],
FROM
(<source query>)
AS <alias for the source data>
UNPIVOT ( [value_column] FOR [unpivot_column] IN ( <column_list> ) ) 
   AS <alias for unpivot>
Where:

--[columns not unpivoted]: 没有被转换的列名。
--[unpivot_column]: 转换的各列所汇总到的单列的名称。
--[value_column]: 转换的各列数据所汇总到的单列的名称。
--<source query>: 源数据。
--<alias for the source data>: 为源数据转换后的表确定一个别名。
--<column_list>:  被转换的列的各列的名称。
--<alias for unpivot>: 转换操作的整个过程的别名。

然后将这张结果表里的数据UNPIVOT回去。

select 
  sid,
  sname,
  sage,
  ssex,
  subject,
  score
from 
(select * from Student_pivot) as sp
UNPIVOT(
  score for subject in ([语文],[数学],[英语]) 
) as t

unpivot

特别注意那些成绩为空的行记录都没有出现！

本文项目地址：

https://github.com/firewang/sql50

(喜欢的话，Star一下)

阅读原文，或者访问该链接可以在线观看（该系列将更新至GitHub,并且托管到read the docs）

https://sql50.readthedocs.io/zh_CN/latest/

参考网址：

https://docs.microsoft.com/zh-cn/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15
https://sql50.readthedocs.io/zh_CN/latest/
https://github.com/firewang/sql50

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2020-03-15，如有侵权请联系 cloudcommunity@tencent.com 删除

https