SQL 解鎖 - Pivot vs Unpivot

DigNo Ape

發佈於SQL

2024/09/08 更新2024/09/08 發佈閱讀 1 分鐘

在MS SQL Server 環境下，如何將sales_unpivot （表1）轉成 sales_pivot （表2）? 再將表2轉回表1?

表1

表2

PIVOT

這裡我們提供了三種方法:

JOIN

這種方式可以通過將相同 region 和 product 的數據進行聯接，從而將不同季度的數據放在同一列。此方式相對簡單直接，對於數據量較小的資料表的情況適用。當季度數據增加時，SQL 語句變得冗長，效能可能會變差，每個季度都需要一個 JOIN。

SELECT
    t1.region,
    t1.product,
    t1.sales AS Q1_sales,
    t2.sales AS Q2_sales,
    t3.sales AS Q3_sales,
    t4.sales AS Q4_sales
FROM 
    (SELECT * FROM sales_unpivot WHERE quarter = 'Q1') t1
JOIN 
    (SELECT * FROM sales_unpivot WHERE quarter = 'Q2') t2
    ON t1.region = t2.region AND t1.product = t2.product
JOIN 
    (SELECT * FROM sales_unpivot WHERE quarter = 'Q3') t3
    ON t1.region = t3.region AND t1.product = t3.product
JOIN 
    (SELECT * FROM sales_unpivot WHERE quarter = 'Q4') t4
    ON t1.region = t4.region AND t1.product = t4.product
ORDER BY t1.region, t1.product;

CASE WHEN

CASE WHEN 可以用來根據季度條件將數據放置到相應的列中。此方式靈活且通用，適用於任何數據庫系統。易於理解，對於有很多不同條件的數據進行處理很有用。如果你的 SQL 數據庫不支援 PIVOT，可以考慮 CASE WHEN 保持程式碼靈活。

SELECT
    region,
    product,
    SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS Q1_sales,
    SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS Q2_sales,
    SUM(CASE WHEN quarter = 'Q3' THEN sales ELSE 0 END) AS Q3_sales,
    SUM(CASE WHEN quarter = 'Q4' THEN sales ELSE 0 END) AS Q4_sales
FROM
    sales_unpivot
GROUP BY
    region, product
ORDER BY
    region, product;

PIVOT

PIVOT 是一種MS SQL Server 內建的 SQL 函數，專門用於產生樞紐分析表。簡潔且易於使用，在需要轉置多個欄位時最為高效。但並非所有的 SQL 數據庫都支持 PIVOT。

SELECT 
    region,
    product,
    [Q1] AS Q1_sales,
    [Q2] AS Q2_sales,
    [Q3] AS Q3_sales,
    [Q4] AS Q4_sales
FROM 
    sales_unpivot
PIVOT (
    SUM(sales)
    FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pivot_table
ORDER BY 
    region, product;

UNPIVOT

我們同樣提供了三種方法:

UNION ALL

這是一種較為直觀的方式，你可以為每一個季度的銷售數據手動建立 SELECT 語句，然後將它們合併在一起。此方法適合數據列有限的情況，但當季度或欄位增多時，SQL 語句會變得冗長且難以維護。

SELECT 
    region,
    product,
    'Q1' AS quarter,
    Q1_sales AS sales
FROM 
    sales_pivot
UNION ALL
SELECT 
    region,
    product,
    'Q2' AS quarter,
    Q2_sales AS sales
FROM 
    sales_pivot
UNION ALL
SELECT 
    region,
    product,
    'Q3' AS quarter,
    Q3_sales AS sales
FROM 
    sales_pivot
UNION ALL
SELECT 
    region,
    product,
    'Q4' AS quarter,
    Q4_sales AS sales
FROM 
    sales_pivot
ORDER BY 
    region, product, quarter;

CROSS APPLY

CROSS APPLY 是另一種方法，它可以更靈活地處理數據。這種方式避免了使用多次的 SELECT 語句。

SELECT 
    region,
    product,
    quarter,
    sales
FROM 
    sales_pivot
CROSS APPLY (
    VALUES 
        ('Q1', Q1_sales),
        ('Q2', Q2_sales),
        ('Q3', Q3_sales),
        ('Q4', Q4_sales)
) AS quarters(quarter, sales)
ORDER BY 
    region, product, quarter;

UNPIVOT

UNPIVOT 是一種MS SQL SERVER內建的 SQL 函數，可以用來將多個欄位旋轉成列，這是一個更為動態且簡潔的方法。UNPIVOT 將四個季度的銷售數據（Q1_sales, Q2_sales, Q3_sales, Q4_sales）變成兩個欄位，即 quarter 和 sales。

SELECT 
    region,
    product,
    quarter,
    sales
FROM sales_pivot
UNPIVOT (
    sales FOR quarter IN (Q1_sales, Q2_sales, Q3_sales, Q4_sales)
) AS unpivoted_table;