SQL透视表二次分组可行性咨询：能否对现有查询结果再次分组？

首先我先把你现有的查询补全并整理好（原查询漏了CTE的WITH关键字），方便后续参考：

WITH QueryTable AS (
    SELECT * FROM TIME 
    WHERE CONVERT(DATETIME,DATE,101) >= '2019-02-24' 
      AND CONVERT(DATETIME,DATE,101) <= '2019-03-02'
), 
Pivoted AS ( 
    SELECT * FROM QueryTable 
    PIVOT (
        max (DURATION) for DATE in (
            [02/24/2019],[02/25/2019],[02/26/2019],
            [02/27/2019],[02/28/2019],[03/01/2019],[03/02/2019]
        )
    ) as pvt 
), 
ProcessTable As ( 
    SELECT EmpID, JOB, ITEM, PITEM, PROJ, 
           [02/24/2019],[02/25/2019],[02/26/2019],
           [02/27/2019],[02/28/2019],[03/01/2019],[03/02/2019], NOTE 
    FROM Pivoted 
    WHERE EmpID = '171' 
    GROUP BY EmpID, JOB, ITEM, PITEM, PROJ, 
             [02/24/2019],[02/25/2019],[02/26/2019],
             [02/27/2019],[02/28/2019],[03/01/2019],[03/02/2019], NOTE 
) 
SELECT * FROM ProcessTable

二次分组的核心是明确你要聚合的字段（比如透视后的日期列）和分组依据的维度，我给你几个常见场景的示例：

场景1：按JOB+PROJ合并记录，聚合每日时长 #

假设你想把同一JOB+PROJ下的多条记录合并，对每日的DURATION求和（也可以用MAX/MIN，根据需求调整），可以直接在透视结果上做二次聚合：

WITH QueryTable AS (
    SELECT * FROM TIME 
    WHERE CONVERT(DATETIME,DATE,101) >= '2019-02-24' 
      AND CONVERT(DATETIME,DATE,101) <= '2019-03-02'
), 
Pivoted AS ( 
    SELECT * FROM QueryTable 
    PIVOT (
        max (DURATION) for DATE in (
            [02/24/2019],[02/25/2019],[02/26/2019],
            [02/27/2019],[02/28/2019],[03/01/2019],[03/02/2019]
        )
    ) as pvt 
)
-- 二次分组逻辑
SELECT 
    EmpID,
    JOB,
    PROJ,
    SUM([02/24/2019]) AS [02/24/2019],
    SUM([02/25/2019]) AS [02/25/2019],
    SUM([02/26/2019]) AS [02/26/2019],
    SUM([02/27/2019]) AS [02/27/2019],
    SUM([02/28/2019]) AS [02/28/2019],
    SUM([03/01/2019]) AS [03/01/2019],
    SUM([03/02/2019]) AS [03/02/2019],
    -- 文本类型的NOTE可以用字符串聚合函数合并，比如SQL Server的STRING_AGG
    STRING_AGG(NOTE, '; ') AS CombinedNotes
FROM Pivoted
WHERE EmpID = '171'
GROUP BY EmpID, JOB, PROJ

场景2：适配动态日期的处理 #

因为你的日期是Web应用动态填充的，生成动态SQL时要注意两个关键点：

• 所有透视后的日期列都要被包裹在聚合函数（SUM/MAX等）中
• 分组依据的字段不能包含需要被聚合的列（比如如果要合并ITEM记录，就不要把ITEM放在GROUP BY里）

关键注意事项 #

• 原查询的ProcessTable里的GROUP BY其实只是做了去重（所有字段都在分组列表里），并没有真正聚合数据；二次分组必须明确聚合逻辑才能合并记录
• 如果NOTE字段需要保留，要么把它加入GROUP BY（但会保留不同NOTE的独立记录），要么用字符串聚合函数合并成一条

如果你能告诉我具体的目标结果样式（比如要合并哪些字段、对时长做什么聚合），可以给出更精准的代码！

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