在做数据统计的时候，行转列，列转行是经常碰到的问题。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,--国内供应商供给数量SouthSupplyINT,--南方供应商供给数量NorthSupplyINT --北方供应商供给数量)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, NorthSupplyFROM ProgrectDetail)TUNPIVOT (SupplyNum FOR Supplier IN(OverseaSupply, NativeSupply, SouthSupply, NorthSupply )) P
执行结果：
以上转载于：/linJie1930906722/p/6036714.html。
亲测有效，超级棒，以下是我的练习
3、数据库为SQLServer
源数据为：
列传行：
