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

时间 2019-12-05 标签 sql 行转列 pivot 列转行 unpivot

在作数据统计的时候，行转列，列转行是常常碰到的问题。case when方式太麻烦了，并且可扩展性不强，能够使用 PIVOT，UNPIVOT比较快速实现行转列，列转行，并且可扩展性强sql

1、行转列测试

一、测试数据准备spa

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

二、行转列sqlcode

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

执行结果：blog

2、列转行数学

一、测试数据准备class

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

二、列转行的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

执行结果：im