Need oracle query that merges two rows with user ids into one row with multiple user columns

I have data in a large table that looks like this:

case_nbr	event_id	event_desc	User_ID
201	1001	Start Discussion
201	1002	Push Agent	U67800
201	1003	Accept	U67800
201	1004	Transfer	U67800
201	1005	Push Agent	U67999
201	1006	Accept	U67999
201	1007	Transfer	U67999
201	1008	Push Agent	U11800
201	1009	Accept	U11800
201	1010	Transfer	U11800
201	1011	Complete	U11800

I need a query where it answers when an item was given to user and who it came from. I’d expect the output to look like this:

case_nbr	TRANSFER_FROM	TRANSFER_TO
201		U67800
201	U67800	U67999
201	U67999	U11800

Can anyone tell me what the query would look like? Thanks!

Answer

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row matching:

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;

Which, for the sample data:

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;

Outputs:

CASE_NBR	TRANSFER_FROM	TRANSFER_TO
201	null	U67800
201	U67800	U67999
201	U67999	U11800

db<>fiddle here