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

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:

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

CodePudding user response：

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