sql的行转列(PIVOT)与列转行(UNPIVOT)

在做数据统计的时候&＃xff0c;行转列&＃xff0c;列转行是经常碰到的问题。case when方式太麻烦了&＃xff0c;而且可扩展性不强&＃xff0c;可以使用 PIVOT&＃xff0c;UNPIVOT比较快速实现行转列&＃xff0c;列转行&＃xff0c;而且可扩展性强

一、行转列

1、测试数据准备

CREATE TABLE [StudentScores]
([UserName] NVARCHAR(20), --学生姓名[Subject] NVARCHAR(30), --科目[Score] FLOAT, --成绩
)INSERT INTO [StudentScores] SELECT &＃39;张三&＃39;, &＃39;语文&＃39;, 80
INSERT INTO [StudentScores] SELECT &＃39;张三&＃39;, &＃39;数学&＃39;, 90
INSERT INTO [StudentScores] SELECT &＃39;张三&＃39;, &＃39;英语&＃39;, 70
INSERT INTO [StudentScores] SELECT &＃39;张三&＃39;, &＃39;生物&＃39;, 85
INSERT INTO [StudentScores] SELECT &＃39;李四&＃39;, &＃39;语文&＃39;, 80
INSERT INTO [StudentScores] SELECT &＃39;李四&＃39;, &＃39;数学&＃39;, 92
INSERT INTO [StudentScores] SELECT &＃39;李四&＃39;, &＃39;英语&＃39;, 76
INSERT INTO [StudentScores] SELECT &＃39;李四&＃39;, &＃39;生物&＃39;, 88
INSERT INTO [StudentScores] SELECT &＃39;码农&＃39;, &＃39;语文&＃39;, 60
INSERT INTO [StudentScores] SELECT &＃39;码农&＃39;, &＃39;数学&＃39;, 82
INSERT INTO [StudentScores] SELECT &＃39;码农&＃39;, &＃39;英语&＃39;, 96
INSERT INTO [StudentScores] SELECT &＃39;码农&＃39;, &＃39;生物&＃39;, 78

2、行转列sql

SELECT * FROM [StudentScores] /*数据源*/
AS P
PIVOT
(SUM(Score/*行转列后 列的值*/) FOR p.Subject/*需要行转列的列*/ IN ([语文],[数学],[英语],[生物]/*列的值*/)
) AS T

执行结果&＃xff1a;

二、列转行

1、测试数据准备

CREATE TABLE ProgrectDetail
(ProgrectName NVARCHAR(20), --工程名称OverseaSupply INT, --海外供应商供给数量NativeSupply INT, --国内供应商供给数量SouthSupply INT, --南方供应商供给数量NorthSupply INT --北方供应商供给数量
)INSERT INTO ProgrectDetail
SELECT &＃39;A&＃39;, 100, 200, 50, 50
UNION ALL
SELECT &＃39;B&＃39;, 200, 300, 150, 150
UNION ALL
SELECT &＃39;C&＃39;, 159, 400, 20, 320
UNION ALL

2、列转行的sql

SELECT P.ProgrectName,P.Supplier,P.SupplyNum
FROM
(SELECT ProgrectName, OverseaSupply, NativeSupply,SouthSupply, NorthSupplyFROM ProgrectDetail
)T
UNPIVOT
(SupplyNum FOR Supplier IN(OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
) P

执行结果&＃xff1a;

分类: SQL Server