如何将聚合元素不同、分组/扩展元素相同的两个PIVOT合并为每行一个ID？

嘿，你选的思路完全没问题——用两个CTE分别对Awd和Xmit做透视，再通过ID关联就能拿到你想要的结果！我给你补全具体的实现方案，假设你的Cat字段有4个固定类别（比如Cat1、Cat2、Cat3、Cat4，记得换成你表中实际的类别值）：

方案一：用CASE表达式实现透视（兼容性更强） #

这种写法比PIVOT更灵活，适配所有SQL方言：

WITH AwdAggregates AS (
    SELECT 
        ID,
        -- 按Cat分组聚合Awd，每个Cat对应一列
        Cat1_Awd = SUM(CASE WHEN Cat = 'Cat1' THEN Awd ELSE 0 END),
        Cat2_Awd = SUM(CASE WHEN Cat = 'Cat2' THEN Awd ELSE 0 END),
        Cat3_Awd = SUM(CASE WHEN Cat = 'Cat3' THEN Awd ELSE 0 END),
        Cat4_Awd = SUM(CASE WHEN Cat = 'Cat4' THEN Awd ELSE 0 END)
    FROM YourActualTableName
    GROUP BY ID
),
XmitAggregates AS (
    SELECT 
        ID,
        -- 同理聚合Xmit
        Cat1_Xmit = SUM(CASE WHEN Cat = 'Cat1' THEN Xmit ELSE 0 END),
        Cat2_Xmit = SUM(CASE WHEN Cat = 'Cat2' THEN Xmit ELSE 0 END),
        Cat3_Xmit = SUM(CASE WHEN Cat = 'Cat3' THEN Xmit ELSE 0 END),
        Cat4_Xmit = SUM(CASE WHEN Cat = 'Cat4' THEN Xmit ELSE 0 END)
    FROM YourActualTableName
    GROUP BY ID
)
-- 关联两个CTE，得到最终结果
SELECT 
    agg.ID,
    agg.Cat1_Awd, agg.Cat2_Awd, agg.Cat3_Awd, agg.Cat4_Awd,
    xagg.Cat1_Xmit, xagg.Cat2_Xmit, xagg.Cat3_Xmit, xagg.Cat4_Xmit
FROM AwdAggregates agg
INNER JOIN XmitAggregates xagg ON agg.ID = xagg.ID
ORDER BY agg.ID;

方案二：用PIVOT运算符实现（符合你提到的思路） #

如果你更习惯用PIVOT语法，也可以这么写：

WITH AwdPivot AS (
    SELECT 
        ID,
        [Cat1] AS Cat1_Awd,
        [Cat2] AS Cat2_Awd,
        [Cat3] AS Cat3_Awd,
        [Cat4] AS Cat4_Awd
    FROM (
        -- 子查询只取需要的字段，减少PIVOT的处理量
        SELECT ID, Cat, Awd FROM YourActualTableName
    ) src
    -- 按Cat透视，聚合Awd
    PIVOT (SUM(Awd) FOR Cat IN ([Cat1], [Cat2], [Cat3], [Cat4])) pvt
),
XmitPivot AS (
    SELECT 
        ID,
        [Cat1] AS Cat1_Xmit,
        [Cat2] AS Cat2_Xmit,
        [Cat3] AS Cat3_Xmit,
        [Cat4] AS Cat4_Xmit
    FROM (
        SELECT ID, Cat, Xmit FROM YourActualTableName
    ) src
    -- 同理透视Xmit
    PIVOT (SUM(Xmit) FOR Cat IN ([Cat1], [Cat2], [Cat3], [Cat4])) pvt
)
-- 关联两个透视结果
SELECT 
    p.ID,
    p.Cat1_Awd, p.Cat2_Awd, p.Cat3_Awd, p.Cat4_Awd,
    xp.Cat1_Xmit, xp.Cat2_Xmit, xp.Cat3_Xmit, xp.Cat4_Xmit
FROM AwdPivot p
INNER JOIN XmitPivot xp ON p.ID = xp.ID
ORDER BY p.ID;

几个重要的调整点： #

• 把YourActualTableName替换成你的真实表名
• 将[Cat1]、[Cat2]等换成你表中实际存在的Cat字段值
• 聚合函数SUM可以根据业务需求换成MAX、MIN、AVG等
• 如果存在某个ID只有Awd数据或只有Xmit数据的情况，把INNER JOIN改成LEFT JOIN就能保留所有ID的记录

这样处理后，你就能得到每行对应唯一ID，同时包含各Cat对应Awd和Xmit聚合值的结果集啦。

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