sql面试题：行转列

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 SELECT ‘D‘, 250, 30, 15, 15

 

 行转列后：

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

 

 

sql面试题：行转列

标签：out   create   color   pre   unp   insert   unpivot   south   国内