sqlserver pivot 多列

在SQL Server中，如果你想对同一列执行多次Pivot操作（也就是多列转换），通常的做法是不能直接通过一次查询完成，因为每个Pivot都是基于单个聚合函数（如SUM, COUNT, AVG等）并重新组织数据的。如果你有多个列需要这样处理，你可能需要组合使用CASE语句或者使用自连接和UNION ALL来实现。

举个例子[^1]，假设我们有一个表格`Sales`，有`Product`, `Month`, 和 `Amount` 列，你想按产品和月份分开展示每个月的总销售额：

SELECT *
FROM (
    SELECT Product, Month, Amount
    FROM Sales
) src
PIVOT (
    SUM(Amount)
    FOR Month IN ([January], [February], [March])
) AS pivoted_data;

但是，如果要针对不同的列（比如`Product`和`Month`）分别进行Pivot，你可能需要创建两个独立的Pivot查询，并将结果合并：

-- Pivot for Product
DECLARE @product_pivot TABLE (Product VARCHAR(50), January INT, February INT, March INT);

INSERT INTO @product_pivot
SELECT Product, SUM(CASE WHEN Month = 'January' THEN Amount ELSE 0 END) as January,
       SUM(CASE WHEN Month = 'February' THEN Amount ELSE 0 END) as February,
       SUM(CASE WHEN Month = 'March' THEN Amount ELSE 0 END) as March
FROM Sales
GROUP BY Product;

-- Pivot for Month
DECLARE @month_pivot TABLE (Month VARCHAR(10), Product1 INT, Product2 INT, Product3 INT); -- 根据实际产品数量调整ProductX

INSERT INTO @month_pivot
SELECT Month, SUM(CASE WHEN Product = 'Product1' THEN Amount ELSE 0 END) as Product1,
       SUM(CASE WHEN Product = 'Product2' THEN Amount ELSE 0 END) as Product2,
       SUM(CASE WHEN Product = 'Product3' THEN Amount ELSE 0 END) as Product3
FROM Sales
GROUP BY Month;

-- Combine the results
SELECT *
FROM @product_pivot p
CROSS APPLY (SELECT * FROM @month_pivot WHERE Month = p.Month) m;