需要oracle查询，将包含用户id的两行合并为包含多个用户列的一行

Question

我有一个大表中的数据，看起来像这样:

User_ID开始讨论Push AgentU67800U67800U67800Push AgentU67999U67999U67999Push AgentU11800U11800U11800U11800

在Oracle 12中，您可以使用MATCH_RECOGNIZE来执行逐行匹配:

SELECT *
FROM   table_name
MATCH_RECOGNIZE (
PARTITION BY case_nbr
ORDER     BY event_id
MEASURES
FIRST(user_id) AS transfer_from,
NEXT(user_id)  AS transfer_to
PATTERN (same_user+)
DEFINE
same_user AS FIRST(user_id) = user_id
OR ( FIRST(user_id) IS NULL AND user_id IS NULL )
)
WHERE transfer_to IS NOT NULL;

对于样本数据:

CREATE TABLE table_name (case_nbr, event_id, event_desc, User_ID) AS
SELECT 201, 1001, 'Start Discussion', NULL     FROM DUAL UNION ALL
SELECT 201, 1002, 'Push Agent',       'U67800' FROM DUAL UNION ALL
SELECT 201, 1003, 'Accept',           'U67800' FROM DUAL UNION ALL
SELECT 201, 1004, 'Transfer',         'U67800' FROM DUAL UNION ALL
SELECT 201, 1005, 'Push Agent',       'U67999' FROM DUAL UNION ALL
SELECT 201, 1006, 'Accept',           'U67999' FROM DUAL UNION ALL
SELECT 201, 1007, 'Transfer',         'U67999' FROM DUAL UNION ALL
SELECT 201, 1008, 'Push Agent',       'U11800' FROM DUAL UNION ALL
SELECT 201, 1009, 'Accept',           'U11800' FROM DUAL UNION ALL
SELECT 201, 1010, 'Transfer',         'U11800' FROM DUAL UNION ALL
SELECT 201, 1011, 'Complete',         'U11800' FROM DUAL;

输出:TRANSFER_TOU67800U67999U11800