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

一、行转列

1、测试数据准备

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

2、行转列sql

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

执行结果：

二、列转行

1、测试数据准备

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

2、列转行的sql

SELECT P.ProgrectName,P.Supplier,P.SupplyNumFROM (    SELECT ProgrectName, OverseaSupply, NativeSupply,           SouthSupply, NorthSupply     FROM ProgrectDetail)TUNPIVOT (    SupplyNum FOR Supplier IN    (OverseaSupply, NativeSupply, SouthSupply, NorthSupply )) P

执行结果：

 

分类: