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

user_1021053 0
Viewed 35

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!

oracle sql
1 Answers

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

user_15092640
Related