如何在单个SQL查询中执行多次UNPIVOT操作

方案一：基于UNPIVOT + JOIN实现 #

通过分别对合同价格和数量字段执行UNPIVOT，提取月份数字作为关联键，再将两个结果集关联，得到合并后的输出：

SELECT
    p.C_FORECAST_YEAR,
    p.CUSTOMER,
    p.ITEM,
    p.C_SALFOR_RVRT,
    p.CONTRACT_MONTH,
    p.CONTRACT_PRICE,
    q.QTY_MONTH,
    q.QTY
FROM (
    SELECT 
        C_FORECAST_YEAR, 
        CUSTOMER,
        ITEM, 
        C_SALFOR_RVRT,
        CONTRACT_MONTH,
        CONTRACT_PRICE,
        -- 提取月份数字，用于匹配对应月份的数量数据
        CAST(RIGHT(CONTRACT_MONTH, LEN(CONTRACT_MONTH) - LEN('CONTRACT_PRICE')) AS INT) AS MONTH_NUM
    FROM (
        SELECT 
            C_FORECAST_YEAR, 
            CUSTOMER,
            ITEM, 
            C_SALFOR_RVRT,
            CONTRACT_PRICE1, CONTRACT_PRICE2, CONTRACT_PRICE3, CONTRACT_PRICE4,
            CONTRACT_PRICE5, CONTRACT_PRICE6, CONTRACT_PRICE7, CONTRACT_PRICE8,
            CONTRACT_PRICE9, CONTRACT_PRICE10, CONTRACT_PRICE11, CONTRACT_PRICE12
        FROM C_SALFOR
    ) AS src
    UNPIVOT (
        CONTRACT_PRICE FOR CONTRACT_MONTH IN (
            CONTRACT_PRICE1, CONTRACT_PRICE2, CONTRACT_PRICE3, CONTRACT_PRICE4,
            CONTRACT_PRICE5, CONTRACT_PRICE6, CONTRACT_PRICE7, CONTRACT_PRICE8,
            CONTRACT_PRICE9, CONTRACT_PRICE10, CONTRACT_PRICE11, CONTRACT_PRICE12
        )
    ) AS unpvt_price
    WHERE C_FORECAST_YEAR = '2023'
) AS p
JOIN (
    SELECT 
        C_FORECAST_YEAR, 
        CUSTOMER,
        ITEM, 
        C_SALFOR_RVRT,
        QTY_MONTH,
        QTY,
        -- 提取月份数字，用于匹配对应月份的价格数据
        CAST(RIGHT(QTY_MONTH, LEN(QTY_MONTH) - LEN('QTY')) AS INT) AS MONTH_NUM
    FROM (
        SELECT 
            C_FORECAST_YEAR, 
            CUSTOMER,
            ITEM, 
            C_SALFOR_RVRT,
            QTY1, QTY2, QTY3, QTY4, QTY5, QTY6,
            QTY7, QTY8, QTY9, QTY10, QTY11, QTY12
        FROM C_SALFOR
    ) AS src
    UNPIVOT (
        QTY FOR QTY_MONTH IN (
            QTY1, QTY2, QTY3, QTY4, QTY5, QTY6,
            QTY7, QTY8, QTY9, QTY10, QTY11, QTY12
        )
    ) AS unpvt_qty
    WHERE C_FORECAST_YEAR = '2023'
) AS q
ON 
    p.C_FORECAST_YEAR = q.C_FORECAST_YEAR
    AND p.CUSTOMER = q.CUSTOMER
    AND p.ITEM = q.ITEM
    AND p.C_SALFOR_RVRT = q.C_SALFOR_RVRT
    AND p.MONTH_NUM = q.MONTH_NUM

方案二：基于CROSS APPLY的更简洁实现 #

使用CROSS APPLY结合VALUES列表模拟UNPIVOT效果，直接匹配对应月份的价格和数量：

SELECT
    src.C_FORECAST_YEAR,
    src.CUSTOMER,
    src.ITEM,
    src.C_SALFOR_RVRT,
    price.CONTRACT_MONTH,
    price.CONTRACT_PRICE,
    qty.QTY_MONTH,
    qty.QTY
FROM C_SALFOR src
CROSS APPLY (
    VALUES
        ('CONTRACT_PRICE1', CONTRACT_PRICE1),
        ('CONTRACT_PRICE2', CONTRACT_PRICE2),
        ('CONTRACT_PRICE3', CONTRACT_PRICE3),
        ('CONTRACT_PRICE4', CONTRACT_PRICE4),
        ('CONTRACT_PRICE5', CONTRACT_PRICE5),
        ('CONTRACT_PRICE6', CONTRACT_PRICE6),
        ('CONTRACT_PRICE7', CONTRACT_PRICE7),
        ('CONTRACT_PRICE8', CONTRACT_PRICE8),
        ('CONTRACT_PRICE9', CONTRACT_PRICE9),
        ('CONTRACT_PRICE10', CONTRACT_PRICE10),
        ('CONTRACT_PRICE11', CONTRACT_PRICE11),
        ('CONTRACT_PRICE12', CONTRACT_PRICE12)
) AS price(CONTRACT_MONTH, CONTRACT_PRICE)
CROSS APPLY (
    VALUES
        ('QTY1', QTY1),
        ('QTY2', QTY2),
        ('QTY3', QTY3),
        ('QTY4', QTY4),
        ('QTY5', QTY5),
        ('QTY6', QTY6),
        ('QTY7', QTY7),
        ('QTY8', QTY8),
        ('QTY9', QTY9),
        ('QTY10', QTY10),
        ('QTY11', QTY11),
        ('QTY12', QTY12)
) AS qty(QTY_MONTH, QTY)
WHERE 
    src.C_FORECAST_YEAR = '2023'
    -- 通过截取字段末尾的数字匹配对应月份
    AND RIGHT(price.CONTRACT_MONTH, LEN(price.CONTRACT_MONTH) - LEN('CONTRACT_PRICE')) = RIGHT(qty.QTY_MONTH, LEN(qty.QTY_MONTH) - LEN('QTY'))

说明 #

两种方案都能实现将两个UNPIVOT的结果合并为同一行，核心逻辑是通过共同维度字段（C_FORECAST_YEAR、CUSTOMER、ITEM、C_SALFOR_RVRT）和月份数字匹配，确保同一月份的合同价格和数量出现在同一行中。

内容的提问来源于stack exchange，提问作者Mark Slagle