一、PIVOT
1.1 语法:PIVOT(聚合函数(列) FOR 列 in (…) )AS P
1.2 测试数据
CREATE TABLE [StudentScores]
(
[UserName] NVARCHAR(20),
[Subject] NVARCHAR(30),
[Score] FLOAT,
)
INSERT INTO [StudentScores] SELECT '张三', '语文', 80
INSERT INTO [StudentScores] SELECT '张三', '数学', 90
INSERT INTO [StudentScores] SELECT '张三', '英语', 70
INSERT INTO [StudentScores] SELECT '张三', '生物', 85
INSERT INTO [StudentScores] SELECT '李四', '语文', 80
INSERT INTO [StudentScores] SELECT '李四', '数学', 92
INSERT INTO [StudentScores] SELECT '李四', '英语', 76
INSERT INTO [StudentScores] SELECT '李四', '生物', 88
INSERT INTO [StudentScores] SELECT '码农', '语文', 60
INSERT INTO [StudentScores] SELECT '码农', '数学', 82
INSERT INTO [StudentScores] SELECT '码农', '英语', 96
INSERT INTO [StudentScores] SELECT '码农', '生物', 78
1.3 行转列sql
SELECT * FROM [StudentScores]
AS P
PIVOT
(
SUM(Score) FOR
p.Subject IN ([语文],[数学],[英语],[生物])
) AS T
二、UNPIVOT
2.1 语法:table_source UNPIVOT(value_column FOR pivot_column IN(<column_list>))
2.2 测试数据
CREATE TABLE ProgrectDetail
(
ProgrectName NVARCHAR(20),
OverseaSupply INT,
NativeSupply INT,
SouthSupply INT,
NorthSupply INT
)
INSERT INTO ProgrectDetail
SELECT 'A', 100, 200, 50, 50
UNION ALL
SELECT 'B', 200, 300, 150, 150
UNION ALL
SELECT 'C', 159, 400, 20, 320
UNION ALL
2.3 列转行sql
SELECT P.ProgrectName,P.Supplier,P.SupplyNum
FROM
(
SELECT ProgrectName, OverseaSupply, NativeSupply,
SouthSupply, NorthSupply
FROM ProgrectDetail
)T
UNPIVOT
(
SupplyNum FOR Supplier IN
(OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
) P
原文地址