含重复数据的SQL行转列实现求助

当前数据表格 #

ActivityName            Username ProcessingTime WaitingTime
----------------------- -------- -------------- -----------
Branch_Maker            nikhil                6          10
Branch_Checker          nikhil                0          69
Director                nikhil                3          57
Branch_Checker          nikhil               32          64
Branch_Checker          nikhil               12          23

期望输出格式 #

Username Branch_Maker Processing Time    Branch_MakerWaitingTime    Branch_Checker ProcessingTime   Branch_Checker Waiting    Director ProcessingTime     Director WaitingTime
-------- ----------------------------    -----------------------     ---------------------------     -----------------------  --------------------------  -------------------
nikhil           6                       10                                        0                    69                         3                       57
nikhil                                                                            32                    64                                                
nikhil                                                                            12                    23   

尝试的SQL语句 #

SELECT 
    ProcessInstanceId, 
    MAX(CASE WHEN ActivityName = 'Branch_Maker' THEN ProcessingTime END) AS Branch_Maker_ProcessingTime, 
    MAX(CASE WHEN ActivityName = 'Branch_Maker' THEN WaitingTime END) AS Branch_Maker_WaitingTime, 
    MAX(CASE WHEN ActivityName = 'Branch_Checker' THEN ProcessingTime END) AS Branch_Checker_ProcessingTime, 
    MAX(CASE WHEN ActivityName = 'Branch_Checker' THEN WaitingTime END) AS Branch_Checker_WaitingTime, 
    MAX(CASE WHEN ActivityName = 'Director' THEN ProcessingTime END) AS Director_ProcessingTime, 
    MAX(CASE WHEN ActivityName = 'Director' THEN WaitingTime END) AS Director_WaitingTime
FROM 
    abc_test_1
GROUP BY 
    ProcessInstanceId;

当前执行结果 #

ProcessInstanceId      Branch_Maker_ProcessingTime Branch_Maker_WaitingTime Branch_Checker_ProcessingTime Branch_Checker_WaitingTime Director_ProcessingTime Director_WaitingTime
---------------------- --------------------------- ------------------------ ----------------------------- -------------------------- ----------------------- --------------------
COB-0000024750-process                           6                       10                             0                         69                       3                   57

问题分析与解决方案 #

你当前的SQL用ProcessInstanceId分组并使用MAX()聚合，导致所有同属一个ProcessInstanceId的记录被合并成一行，这和你期望保留多行的需求不符。要实现目标，需要先给每个用户的同一活动类型的记录分配行号，再按用户和行号分组，确保同一行号下的不同活动记录被整合到同一行，不同行号的重复活动（比如Branch_Checker的多条记录）分成单独行。

修正后的SQL如下：

WITH ranked_data AS (
    SELECT 
        Username,
        ActivityName,
        ProcessingTime,
        WaitingTime,
        -- 为每个用户的同一活动类型分配行号
        ROW_NUMBER() OVER (PARTITION BY Username, ActivityName ORDER BY (SELECT NULL)) AS rn
    FROM abc_test_1
)
SELECT 
    Username,
    MAX(CASE WHEN ActivityName = 'Branch_Maker' THEN ProcessingTime END) AS "Branch_Maker Processing Time",
    MAX(CASE WHEN ActivityName = 'Branch_Maker' THEN WaitingTime END) AS "Branch_Maker WaitingTime",
    MAX(CASE WHEN ActivityName = 'Branch_Checker' THEN ProcessingTime END) AS "Branch_Checker ProcessingTime",
    MAX(CASE WHEN ActivityName = 'Branch_Checker' THEN WaitingTime END) AS "Branch_Checker Waiting",
    MAX(CASE WHEN ActivityName = 'Director' THEN ProcessingTime END) AS "Director ProcessingTime",
    MAX(CASE WHEN ActivityName = 'Director' THEN WaitingTime END) AS "Director WaitingTime"
FROM ranked_data
GROUP BY Username, rn
ORDER BY Username, rn;

说明 #

1. CTE部分：使用ROW_NUMBER()函数，按Username和ActivityName分区，给同一用户的同一活动类型的每条记录分配唯一行号，这样Branch_Checker的3条记录会被标记为1、2、3，Branch_Maker和Director的各1条记录标记为1。
2. 分组查询：按Username和行号rn分组，用MAX(CASE...)将不同活动的时间字段转置为列，同一行号下的不同活动会被整合到同一行，不同行号的重复活动则生成单独的行，最终得到你期望的输出格式。

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