如何为带动态列标题的Pivot查询添加总计列？

要给动态列的Pivot查询添加有效总计列，核心是生成动态求和表达式，把所有动态列的值（处理NULL为0）累加。以下是修改后的完整代码：

-- Construct the dynamic PIVOT query
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @cols_sum AS NVARCHAR(MAX)

-- Get the distinct rates
SELECT @cols = COALESCE(@cols + ', ', '') + QUOTENAME(rate)
FROM (SELECT DISTINCT rate FROM #PayDetailNAme pay JOIN #DutyInfo2 duty ON pay.DUTY_ID = duty.Dutyid ) AS rate;

-- 生成总计列的动态求和表达式（自动处理NULL为0）
SELECT @cols_sum = COALESCE(@cols_sum + ' + ', '') + 'ISNULL(' + QUOTENAME(rate) + ', 0)'
FROM (SELECT DISTINCT rate FROM #PayDetailNAme pay JOIN #DutyInfo2 duty ON pay.DUTY_ID = duty.Dutyid ) AS rate;

DECLARE @query AS NVARCHAR(MAX)

-- 构造包含总计列的动态PIVOT查询
SET @query = '
SELECT
    [start],
    [end],
    EMPLOYEE,
    pin,
    ' + @cols + ',
    ' + @cols_sum + ' AS TotalHours  -- 新增每行的总计列
FROM 
(
SELECT 
''' + @start + ''' as [Start],
''' + @end + ''' as [End],
EMPLOYEE,
pin, 
Rate,
ISNULL(hours,0) as hours

from #PayDetailNAme pay 
    JOIN #DutyInfo2 duty 
        ON pay.DUTY_ID = duty.Dutyid
) 
AS SourceTable
PIVOT
(
  SUM(hours)
  FOR rate IN (' + @cols + ')
) 
AS PivotTable
WHERE COALESCE(' + @cols + ') IS NOT NULL
;';

-- 执行动态查询
EXEC sp_executesql @query;

关键修改说明： #

1. 新增@cols_sum变量：自动拼接所有动态Rate列的求和逻辑，用ISNULL(列名,0)避免NULL值导致总和为空。
2. 在SELECT中添加总计列：把动态求和表达式作为TotalHours列加入结果集，直接得到每行的工时总和。
3. 保留原查询逻辑：原有的Pivot逻辑、过滤条件均保持不变，仅新增总计列。

如果需要添加整表的总计行（而非每行的总计列），可以在Pivot结果后用UNION ALL结合ROLLUP实现，但根据你的需求，上述方案更贴合"每行总计"的场景。

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