如何用UNPIVOT/CROSS APPLY替代UNION ALL实现两年数据对比？

现有表结构与数据 #

临时表#CarPartSales存储汽车配件销售数据，结构及初始化代码如下：

CREATE TABLE #CarPartSales (
        Make        VARCHAR(100)
,       Model       VARCHAR(100)
,       Part        VARCHAR(100)
,       Color       VARCHAR(100)
,       SalesYear   INT
,       TotalSold   DECIMAL(18,2)
,       Invoices    DECIMAL(18,2)
,       TotalPrice  DECIMAL(18,2)
)

INSERT INTO #CarPartSales VALUES 
    ('Chevy','Nova','Door','Red',2023, 120.00, 100.00, 900.50)
,   ('Chevy','Nova','Door','Red',2022, 100.00, 90.00, 850.25)
,   ('Chevy','Nova','Door','Black',2023, 70.00, 50.00, 450.75)
,   ('Chevy','Nova','Door','Black',2022, 20.00, 8.00, 200.00)
,   ('Ford','Fairlaine','Hood','White',2023, 100.00, 75.00, 780.35)
,   ('Ford','Fairlaine','Hood','White',2022, 70.00, 65.00, 650.20)
,   ('Dodge','Aspen','Fender','Green',2023, 30.00, 10.00, 125.00)
,   ('Dodge','Aspen','Fender','Green',2022, 10.00, 3.00, 75.55)

需求与原始实现 #

需要生成包含Make、Model、Part、Color、指标类别、2022年数值、2023年数值及差值的结果集。

最初采用多次UNION ALL实现，代码如下：

SELECT      Make
,           Model
,           Part
,           Color
,           Category        = 'TotalSold'
,           PreviousValue   = SUM(CASE WHEN SalesYear = 2022 THEN TotalSold END)
,           CurrentValue    = SUM(CASE WHEN SalesYear = 2023 THEN TotalSold END)
,           ValueChange     = SUM(CASE WHEN SalesYear = 2023 THEN TotalSold END) - SUM(CASE WHEN SalesYear = 2022 THEN TotalSold END)
FROM        #CarPartSales
GROUP BY    Make
,           Model
,           Part
,           Color

UNION ALL

SELECT      Make
,           Model
,           Part
,           Color
,           Category        = 'Invoices'
,           PreviousValue   = SUM(CASE WHEN SalesYear = 2022 THEN Invoices END)
,           CurrentValue    = SUM(CASE WHEN SalesYear = 2023 THEN Invoices END)
,           ValueChange     = SUM(CASE WHEN SalesYear = 2023 THEN Invoices END) - SUM(CASE WHEN SalesYear = 2022 THEN Invoices END)
FROM        #CarPartSales
GROUP BY    Make
,           Model
,           Part
,           Color

UNION ALL

SELECT      Make
,           Model
,           Part
,           Color
,           Category        = 'TotalPrice'
,           PreviousValue   = SUM(CASE WHEN SalesYear = 2022 THEN TotalPrice END)
,           CurrentValue    = SUM(CASE WHEN SalesYear = 2023 THEN TotalPrice END)
,           ValueChange     = SUM(CASE WHEN SalesYear = 2023 THEN TotalPrice END) - SUM(CASE WHEN SalesYear = 2022 THEN TotalPrice END)
FROM        #CarPartSales
GROUP BY    Make
,           Model
,           Part
,           Color

ORDER BY 1,2,3,4,5

但实际场景中有50余个指标类别，重复编写UNION ALL的方式过于繁琐，因此希望通过UNPIVOT或CROSS APPLY简化实现。

优化后的实现（CTE + UNPIVOT） #

通过CTE结合UNPIVOT将多列指标转置为行，再统一聚合计算，大幅减少重复代码，实现如下：

;WITH cte AS (
SELECT      SalesYear, Make, Model, Part, Color, ColumnName, value
FROM        (
    SELECT      SalesYear, Make, Model, Part, Color, TotalSold, Invoices, TotalPrice
    FROM        #CarPartSales
            ) AS SourceTable
UNPIVOT     (   
                value FOR ColumnName IN (
                        TotalSold
                ,       Invoices
                ,       TotalPrice)
            ) as Alias
)
SELECT      Make
,           Model
,           Part
,           Color
,           ColumnName
,           CurrentValue  = SUM(CASE WHEN SalesYear = 2023 THEN value END)
,           PreviousValue = SUM(CASE WHEN SalesYear = 2022 THEN value END)
,           ValueChange   = SUM(CASE WHEN SalesYear = 2023 THEN value END) - SUM(CASE WHEN SalesYear = 2022 THEN value END)
FROM        cte
GROUP BY    Make
,           Model
,           Part
,           Color
,           ColumnName
ORDER BY    1,2,3,4,5

后续新增指标时，仅需在UNPIVOT的IN列表中添加对应列名即可，无需重复编写聚合逻辑。

内容的提问来源于stack exchange，提问作者Jeff Brady