SQL Server使用PIVOT函数实现行转列的实操问题咨询

方案1：固定列PIVOT实现（已知所有需要展示的费用项名称时使用） #

你可以直接将现有聚合查询作为数据源，套入PIVOT函数即可，参考代码如下：

-- 先将你原有的分组聚合逻辑作为CTE
WITH charge_agg AS (
    SELECT o.order_id,  
           chr.charge_name,
           Sum(chr.amount) AS amount
    FROM   [tms].[orders] o
           INNER JOIN [tms].[extra_charges_details] chr
                   ON o.sourcetms = chr.sourcetms
                      AND o.order_id = chr.[order]
           INNER JOIN [tms].[probills] p
                   ON o.sourcetms = p.sourcetms
                      AND o.order_id = p.order_id
           INNER JOIN [tms].[customers] c on o.UniqueCustomerID=c.UniqueCompanyID
    WHERE  o.[date] >= '2021-01-01'
    GROUP  BY o.order_id, chr.charge_name
)
SELECT 
    order_id AS [Row Labels],
    -- 按你需要的费用项枚举所有列，没有匹配值的默认返回NULL，用ISNULL可以替换为0
    ISNULL([FSC], 0) AS [FSC],
    ISNULL([FUEL], 0) AS [FUEL],
    ISNULL([HR CITY], 0) AS [HR CITY],
    ISNULL([HRS HWY], 0) AS [HRS HWY]
    -- 其他费用项按相同格式补充即可
FROM charge_agg
PIVOT (
    -- 因为已经提前做过聚合，此处也可以用MAX函数，结果一致
    SUM(amount)
    FOR charge_name IN ([FSC], [FUEL], [HR CITY], [HRS HWY] /* 其他费用项同步枚举 */)
) AS pivot_result

方案2：动态PIVOT实现（费用项不固定、经常新增时使用） #

如果charge_name的取值会动态增加，不想每次手动修改SQL里的枚举项，可以用动态SQL自动生成列，参考代码如下：

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

-- 自动获取所有不重复的费用项名称，生成PIVOT需要的列列表
SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(charge_name) 
                      FROM [tms].[extra_charges_details] chr
                      -- 如果需要和你原有查询的筛选范围一致，可以加如下关联条件
                      -- WHERE EXISTS (SELECT 1 FROM [tms].[orders] o WHERE o.sourcetms = chr.sourcetms AND o.order_id = chr.[order] AND o.[date] >= '2021-01-01')
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 拼接最终的执行SQL
SET @query = N'
WITH charge_agg AS (
    SELECT o.order_id,  
           chr.charge_name,
           Sum(chr.amount) AS amount
    FROM   [tms].[orders] o
           INNER JOIN [tms].[extra_charges_details] chr
                   ON o.sourcetms = chr.sourcetms
                      AND o.order_id = chr.[order]
           INNER JOIN [tms].[probills] p
                   ON o.sourcetms = p.sourcetms
                      AND o.order_id = p.order_id
           INNER JOIN [tms].[customers] c on o.UniqueCustomerID=c.UniqueCompanyID
    WHERE  o.[date] >= ''2021-01-01''
    GROUP  BY o.order_id, chr.charge_name
)
SELECT order_id AS [Row Labels], ' + @cols + N'
FROM charge_agg
PIVOT (
    SUM(amount)
    FOR charge_name IN (' + @cols + N')
) AS pivot_result'

-- 执行动态SQL
EXEC sp_executesql @query

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