动态Pivot查询中合并汇总行至单行的问题求助

问题描述 #

现有动态Pivot查询存在两个问题：

• 汇总行（Total）按技术人员拆分显示，无法合并为单行
• Invoiced列汇总值错误，但Total工时列汇总正确

当前查询结果 #

期望结果 #

修改后的查询代码 #

DECLARE @FRDATE DATE = /* SELECT FROM OINV T0 WHERE T0.DocDate >= */  [%0];
DECLARE @TODATE DATE = /* SELECT FROM OINV T0 WHERE T0.DocDate <= */  [%1];
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

SELECT @columns = STUFF((SELECT DISTINCT ',' + QUOTENAME(CONCAT(firstName, ' ', lastName)) [Technician]
                         FROM SCL6
                                  INNER JOIN OHEM ON SCL6.Technician = OHEM.empID
                                  INNER JOIN HEM6 ON HEM6.[empID] = OHEM.[empID]
                                  INNER JOIN HTM1 ON HTM1.[empID] = OHEM.[empID]
                         WHERE HEM6.roleID = -2 AND HTM1.teamID IN (1, 2) AND OHEM.Active = 'Y'
                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
                         , 1, 1, '');

DECLARE @techTime NVARCHAR(MAX);
SET @techTime = ' /* section of code for calculating tech time';

DECLARE @invoiceQuantity NVARCHAR(MAX);
SET @invoiceQuantity = ' /* section of code for calculating amount invoiced ';

SELECT @sql = '
;WITH CTE AS (
    SELECT T0.callID,
           T4.StartDate,
           T0.custMrName,
           CONCAT(OHEM.firstName, '' '', OHEM.lastName) AS Tech,
           ' + @techTime + ' AS Dur,
           B.Invoiced
    FROM OSCL T0
             INNER JOIN SCL6 T4 ON T0.callID = T4.SrcvCallID
             INNER JOIN OHEM ON T4.Technician = OHEM.empID
             INNER JOIN HEM6 ON HEM6.[empID] = OHEM.[empID]
             INNER JOIN HTM1 ON HTM1.[empID] = OHEM.[empID]
             ' + @invoiceQuantity + '
    WHERE T4.[Close] = ''Y'' AND
          T4.Technician <> 104 AND
          HEM6.roleID = -2 AND
          HTM1.teamID IN (1, 2) AND
          T0.customer <> ''C003435'' AND
          T4.StartDate BETWEEN @FRDATE AND @TODATE
    GROUP BY T0.callID, T4.StartDate, OHEM.firstName, OHEM.lastName, T4.Technician, T4.StartTime, T4.EndDate, T4.ChkInTime, T4.ChkOutTime, T4.EndTime, T4.ChkInDate, T4.ChkOutDate, B.Invoiced, T0.custMrName
),
-- 生成明细数据的Pivot结果
PivotDetail AS (
    SELECT 
        callID,
        StartDate,
        custMrName,
        ' + @columns + ',
        SUM(Dur) OVER (PARTITION BY callID, StartDate, custMrName) AS Total,
        Invoiced
    FROM CTE
    PIVOT (SUM(Dur) FOR Tech IN (' + @columns + ')) p
    GROUP BY callID, StartDate, custMrName, Invoiced, ' + @columns + '
),
-- 生成汇总行数据
SummaryRow AS (
    SELECT 
        NULL AS callID,
        NULL AS StartDate,
        ''Total'' AS custMrName,
        ' + REPLACE(@columns, ',', ', SUM(') + ' AS ' + REPLACE(@columns, ',', ', ') + ',
        SUM(Total) AS Total,
        SUM(Invoiced) AS Invoiced
    FROM PivotDetail
)
-- 合并明细和汇总行
SELECT 
    callID,
    StartDate,
    custMrName,
    ' + @columns + ',
    Total,
    Invoiced,
    CASE WHEN ISNULL(Invoiced, 0) = 0 THEN NULL ELSE CAST(CAST(ROUND(Invoiced / NULLIF(Total, 0) * 100,0) AS INT) AS VARCHAR) + ''%'' END AS [Chargeable Utilization]
FROM PivotDetail
UNION ALL
SELECT 
    callID,
    StartDate,
    custMrName,
    ' + @columns + ',
    Total,
    Invoiced,
    CASE WHEN ISNULL(Invoiced, 0) = 0 THEN NULL ELSE CAST(CAST(ROUND(Invoiced / NULLIF(Total, 0) * 100,0) AS INT) AS VARCHAR) + ''%'' END AS [Chargeable Utilization]
FROM SummaryRow
ORDER BY 
    CASE WHEN custMrName = ''Total'' THEN 1 ELSE 0 END,
    StartDate ASC';

EXEC sp_executesql @sql, N'@FRDATE DATE, @TODATE DATE', @FRDATE, @TODATE

关键修改说明 #

1. 移除冗余数据生成：删除原CTE中UNION ALL后的重复查询（原代码中人为生成每条记录的Tech=Total副本），这是导致汇总行拆分、Invoiced重复计算的核心原因。
2. 重构Pivot逻辑：
   • 先对明细数据做Pivot，通过窗口函数SUM(Dur) OVER (PARTITION BY callID...)计算每条工单的Total工时
   • 单独生成汇总行，直接对Pivot后的明细数据按列求和，确保每个技术人员的工时、总工时、Invoiced值都正确累加
3. 合并数据排序：通过CASE WHEN custMrName = 'Total' THEN 1 ELSE 0 END确保汇总行显示在所有明细行之后，同时保留原有的日期排序逻辑

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