SQL列转行

行转列

SELECT ProgrectName, 'OverseaSupply' AS Supplier, 
        MAX(OverseaSupply) AS 'SupplyNum'  -- 可以写max，也可以写mean，min，因为都是一样的
FROM ProgrectDetail 
GROUP BY ProgrectName 

UNION ALL 

SELECT ProgrectName, 'NativeSupply' AS Supplier, 
        MAX(NativeSupply) AS 'SupplyNum' 
FROM ProgrectDetail 
GROUP BY ProgrectName 

UNION ALL 

SELECT ProgrectName, 'SouthSupply' AS Supplier, 
        MAX(SouthSupply) AS 'SupplyNum' 
FROM ProgrectDetail 
GROUP BY ProgrectName 

UNION ALL 

SELECT ProgrectName, 'NorthSupply' AS Supplier, 
        MAX(NorthSupply) AS 'SupplyNum' 
FROM ProgrectDetail 
GROUP BY ProgrectName

用UNPIVOT 实现如下：

SELECT ProgrectName,Supplier,SupplyNum 
FROM  
( 
    SELECT ProgrectName, OverseaSupply, NativeSupply, 
           SouthSupply, NorthSupply 
     FROM ProgrectDetail 
)T 
UNPIVOT  
( 
    SupplyNum FOR Supplier IN 
    (OverseaSupply, NativeSupply, SouthSupply, NorthSupply ) 
) P