如何在SQL Server中对多列执行Pivot（透视）操作

原始EMPLOYEE表 #

期望结果表 #

你当前的错误尝试代码 #

SELECT 
    Name, [Sales Mgr] AS 'Sales Mgr Billed', [VP] AS 'VP Billed'
FROM
    (SELECT 
         Name, Role, Billed$, Revenue 
     FROM 
         EMPLOYEE) src
PIVOT
    (SUM(Billed$)
        FOR Role IN ( [Sales Mgr], [VP])
    ) AS pvBilled
PIVOT
    (SUM(Revenue)
         FOR Role IN ( [Sales Mgr], [VP])
    ) AS pvRevenue

问题分析 #

连续两次Pivot的方式逻辑错误：第一次Pivot执行后，原始的Role列已经被展开为[Sales Mgr]和[VP]列，第二次Pivot无法找到Role列作为透视依据，同时会导致结果集产生不必要的笛卡尔积，最终输出错误数据。

解决方案：Cross Apply + 单次Pivot #

要同时透视Billed$和Revenue两个指标，需要先通过CROSS APPLY将这两个指标拆分为行级数据，再结合角色名生成新的透视列名，最后执行单次Pivot即可：

SELECT 
    Name,
    [Sales Mgr_Billed$] AS [Sales Mgr Billed],
    [Sales Mgr_Revenue] AS [Sales Mgr Revenue],
    [VP_Billed$] AS [VP Billed],
    [VP_Revenue] AS [VP Revenue]
FROM (
    SELECT 
        Name,
        CONCAT(Role, '_', ColumnName) AS PivotColumn,
        ColumnValue
    FROM 
        EMPLOYEE
    CROSS APPLY (
        -- 将Billed$和Revenue转为行数据
        VALUES 
            ('Billed$', Billed$),
            ('Revenue', Revenue)
    ) AS CA (ColumnName, ColumnValue)
) AS SourceData
PIVOT (
    -- 每个Name+Role+指标组合唯一，MAX/SUM均可
    MAX(ColumnValue)
    FOR PivotColumn IN (
        [Sales Mgr_Billed$], 
        [Sales Mgr_Revenue], 
        [VP_Billed$], 
        [VP_Revenue]
    )
) AS PivotResult

替代方案：条件聚合（更简洁） #

如果允许不严格使用Pivot语法，条件聚合的写法更直观高效：

SELECT 
    Name,
    MAX(CASE WHEN Role = 'Sales Mgr' THEN Billed$ END) AS [Sales Mgr Billed],
    MAX(CASE WHEN Role = 'Sales Mgr' THEN Revenue END) AS [Sales Mgr Revenue],
    MAX(CASE WHEN Role = 'VP' THEN Billed$ END) AS [VP Billed],
    MAX(CASE WHEN Role = 'VP' THEN Revenue END) AS [VP Revenue]
FROM EMPLOYEE
GROUP BY Name

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