(MSSQL)資料轉置PIVOT(Row2Col)

Syntax

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;

案例

資料表 Customer_Order 內容如下：

客戶每月訂單總數

SELECT YEAR(pvt.OrderDate) AS [Year], MONTH(pvt.OrderDate) AS [MONTH], SUM(pvt.CustomerA) AS CustomerA, SUM(pvt.CustomerB) AS CustomerB
FROM (
    SELECT OrderID, Customer, OrderDate, Amount
    FROM Customer_Order
) p
PIVOT
(
    (OrderID)
    FOR Customer IN ([CustomerA], [CustomerB])
) AS pvt
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY YEAR(pvt.OrderDate), MONTH(pvt.OrderDate)

客戶每月下單總量

SELECT [YEAR],[MONTH],ISNULL([CustomerA],0) AS CustomerA, ISNULL([CustomerB],0) AS CustomerB
FROM (
    SELECT YEAR(OrderDate) AS 'YEAR', MONTH(OrderDate) AS 'MONTH',Customer, Amount
    FROM Customer_Order
) p
PIVOT
(
    SUM(Amount) 
    FOR Customer IN ([CustomerA], [CustomerB])
) AS pvt

客戶每次下單均量

SELECT [YEAR],ISNULL([CustomerA],0) AS CustomerA, ISNULL([CustomerB],0) AS CustomerB
FROM (
    SELECT YEAR(OrderDate) AS 'YEAR',Customer, Amount
    FROM Customer_Order
) p
PIVOT
(
    AVG(Amount) 
    FOR Customer IN ([CustomerA], [CustomerB])
) AS pvt