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

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

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

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

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