TSQL如何通过PIVOT按requestId行转列获取各groupId的projMan与apvStatus

方案1：条件聚合（最推荐，性能最优） #

你原来的多JOIN方案需要多次扫描临时表，性能差，改用条件聚合仅需扫描1次源表即可完成行转列，完全适配非数值字段场景。因为每个requestId+groupId唯一，用MAX/MIN聚合都能精准取到对应值，不会有计算误差。
对应的SQL代码如下：

SELECT 
    requestId,
    MAX(CASE WHEN groupId = 1 THEN projMan END) AS projMan1,
    MAX(CASE WHEN groupId = 1 THEN apvStatus END) AS apvStatus1,
    MAX(CASE WHEN groupId = 2 THEN projMan END) AS projMan2,
    MAX(CASE WHEN groupId = 2 THEN apvStatus END) AS apvStatus2,
    MAX(CASE WHEN groupId = 3 THEN projMan END) AS projMan3,
    MAX(CASE WHEN groupId = 3 THEN apvStatus END) AS apvStatus3,
    MAX(CASE WHEN groupId = 4 THEN projMan END) AS projMan4,
    MAX(CASE WHEN groupId = 4 THEN apvStatus END) AS apvStatus4,
    MAX(CASE WHEN groupId = 5 THEN projMan END) AS projMan5,
    MAX(CASE WHEN groupId = 5 THEN apvStatus END) AS apvStatus5,
    -- 如不需要denialReason可直接删除该行
    MAX(CASE WHEN groupId = 1 THEN denialReason END) AS denialReason
INTO #TEMPBAOrganized
FROM #TEMPBULKAPPROVAL
WHERE groupId BETWEEN 1 AND 5
GROUP BY requestId

方案2：PIVOT实现（适配你之前的尝试方向） #

PIVOT本身也支持非数值字段，只要使用字符串兼容的聚合函数即可，写法参考：

SELECT 
    requestId,
    [1] AS projMan1, [11] AS apvStatus1,
    [2] AS projMan2, [21] AS apvStatus2,
    [3] AS projMan3, [31] AS apvStatus3,
    [4] AS projMan4, [41] AS apvStatus4,
    [5] AS projMan5, [51] AS apvStatus5,
    denialReason
INTO #TEMPBAOrganized
FROM (
    SELECT 
        requestId, 
        groupId, 
        groupId * 10 + 1 AS groupStatusId,
        projMan, 
        apvStatus,
        MAX(CASE WHEN groupId=1 THEN denialReason END) OVER(PARTITION BY requestId) AS denialReason
    FROM #TEMPBULKAPPROVAL
    WHERE groupId BETWEEN 1 AND 5
) AS src
PIVOT (
    MAX(projMan) FOR groupId IN ([1],[2],[3],[4],[5])
) AS pvtProj
PIVOT (
    MAX(apvStatus) FOR groupStatusId IN ([11],[21],[31],[41],[51])
) AS pvtStatus

两种方案性能远高于多JOIN写法，更推荐条件聚合方案，写法更简洁易维护，没有多次PIVOT的额外开销。

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