如何在SQL Server中实现多列Pivot透视并整合多表数据

1. 用临时表/表变量存储中间透视结果再关联 #

完全可以这么做，这是处理复杂透视场景的常用思路，尤其适合分步验证每个透视结果的正确性。

比如先把Orders和Calculations的Tag列透视结果存入临时表：

-- 存储Tag列的透视结果
SELECT *
INTO #PivotedTags
FROM (
    SELECT o.OrderID, c.Tag, c.Value
    FROM Orders o
    JOIN Calculations c ON o.OrderID = c.OrderID
) AS SourceData
PIVOT (
    MAX(Value) -- 根据实际需求选择聚合函数（SUM/AVG等）
    FOR Tag IN ([Tag1], [Tag2], [Tag3]) -- 替换为你的实际Tag值
) AS PivotTable;

之后再基于这个临时表，关联其他透视结果（比如Calculations的其他列、发票/支付表的透视数据）：

-- 关联其他表的透视结果
SELECT 
    pt.OrderID,
    pt.[Tag1], pt.[Tag2], pt.[Tag3],
    pc.[CalculationCol1], pc.[CalculationCol2],
    pi.[InvoiceType1], pi.[InvoiceType2],
    pp.[PaymentMethod1], pp.[PaymentMethod2]
FROM #PivotedTags pt
-- 关联Calculations其他列的透视结果
JOIN (
    SELECT OrderID, [Col1] AS CalculationCol1, [Col2] AS CalculationCol2
    FROM (
        SELECT OrderID, CalculationType, CalculationValue
        FROM Calculations
    ) AS Source
    PIVOT (
        SUM(CalculationValue)
        FOR CalculationType IN ([Col1], [Col2])
    ) AS PivotCalc
) pc ON pt.OrderID = pc.OrderID
-- 关联发票表的透视结果
JOIN (
    SELECT OrderID, [InvoiceA] AS InvoiceType1, [InvoiceB] AS InvoiceType2
    FROM (
        SELECT OrderID, InvoiceType, Amount
        FROM Invoices
    ) AS Source
    PIVOT (
        MAX(Amount)
        FOR InvoiceType IN ([InvoiceA], [InvoiceB])
    ) AS PivotInvoice
) pi ON pt.OrderID = pi.OrderID
-- 关联支付表的透视结果
JOIN (
    SELECT OrderID, [CreditCard] AS PaymentMethod1, [Cash] AS PaymentMethod2
    FROM (
        SELECT OrderID, PaymentMethod, Amount
        FROM Payments
    ) AS Source
    PIVOT (
        SUM(Amount)
        FOR PaymentMethod IN ([CreditCard], [Cash])
    ) AS PivotPayment
) pp ON pt.OrderID = pp.OrderID;

-- 用完临时表记得删除
DROP TABLE #PivotedTags;

如果偏好表变量，把#PivotedTags换成@PivotedTags即可，语法类似：

DECLARE @PivotedTags TABLE (OrderID INT, Tag1 VARCHAR(50), Tag2 VARCHAR(50), Tag3 VARCHAR(50));
INSERT INTO @PivotedTags
SELECT ... -- 同临时表的透视查询

2. 用CTE组合多维度透视，避免临时表 #

如果不想创建临时对象，可以用CTE（公共表表达式）把各个透视逻辑拆分，再在主查询里关联，代码更紧凑：

WITH PivotedTags AS (
    SELECT o.OrderID, c.Tag, c.Value
    FROM Orders o
    JOIN Calculations c ON o.OrderID = c.OrderID
    PIVOT (
        MAX(Value)
        FOR Tag IN ([Tag1], [Tag2], [Tag3])
    ) AS pt
),
PivotedCalculations AS (
    SELECT OrderID, [Col1] AS CalculationCol1, [Col2] AS CalculationCol2
    FROM Calculations
    PIVOT (
        SUM(CalculationValue)
        FOR CalculationType IN ([Col1], [Col2])
    ) AS pc
),
PivotedInvoices AS (
    SELECT OrderID, [InvoiceA] AS InvoiceType1, [InvoiceB] AS InvoiceType2
    FROM Invoices
    PIVOT (
        MAX(Amount)
        FOR InvoiceType IN ([InvoiceA], [InvoiceB])
    ) AS pi
),
PivotedPayments AS (
    SELECT OrderID, [CreditCard] AS PaymentMethod1, [Cash] AS PaymentMethod2
    FROM Payments
    PIVOT (
        SUM(Amount)
        FOR PaymentMethod IN ([CreditCard], [Cash])
    ) AS pp
)
SELECT 
    pt.OrderID,
    pt.[Tag1], pt.[Tag2], pt.[Tag3],
    pc.CalculationCol1, pc.CalculationCol2,
    pi.InvoiceType1, pi.InvoiceType2,
    pp.PaymentMethod1, pp.PaymentMethod2
FROM PivotedTags pt
JOIN PivotedCalculations pc ON pt.OrderID = pc.OrderID
JOIN PivotedInvoices pi ON pt.OrderID = pi.OrderID
JOIN PivotedPayments pp ON pt.OrderID = pp.OrderID;

3. 单次Pivot处理多列（适用于同分组维度） #

如果Calculations中的多个列都是基于同一个分组键（比如OrderID），可以在一次Pivot中通过多个聚合函数处理，或者嵌套Pivot子句：

SELECT 
    OrderID,
    [Tag1], [Tag2], [Tag3],
    [Col1], [Col2]
FROM (
    SELECT 
        OrderID,
        CONCAT('Tag_', Tag) AS PivotCol, Value
    FROM Calculations
    UNION ALL
    SELECT 
        OrderID,
        CONCAT('Calc_', CalculationType) AS PivotCol, CalculationValue
    FROM Calculations
) AS CombinedSource
PIVOT (
    MAX(Value)
    FOR PivotCol IN ([Tag_Tag1], [Tag_Tag2], [Tag_Tag3], [Calc_Col1], [Calc_Col2])
) AS MultiPivot;

注意事项 #

• 临时表比表变量更适合大数据量场景，因为SQL Server会为临时表生成统计信息，优化查询计划；表变量则适合小数据集，无需清理。
• 所有关联操作必须确保分组键（比如OrderID）唯一或关联逻辑正确，避免出现笛卡尔积导致数据膨胀。
• 发票和支付表的透视逻辑和Calculations完全一致，只需替换对应的列名和聚合函数即可。

内容的提问来源于stack exchange，提问作者Arian Aljevic