Oracle SQL Pivot Multiple Columns

Question

I have a data set where 1 LICENSE_ID has been split into multiple rows. I would like to combine the rows so I have 1 row per PERSON_ID and LICENSE_ID combination.

Here is how my data is formatted:

SELECT '123456789' AS PERSON_ID, '1' AS LICENSE_ID, 'NUMBER' AS FIELD, '123' AS EXPECTED, '124' AS ACTUAL, 'UPDATE' AS ACTION FROM DUAL UNION ALL
SELECT '123456789' AS PERSON_ID, '1' AS LICENSE_ID, 'ISSUE_DT' AS FIELD, '43498' AS EXPECTED, '43498' AS ACTUAL, 'NA' AS ACTION FROM DUAL UNION ALL
SELECT '123456789' AS PERSON_ID, '2' AS LICENSE_ID, 'NUMBER' AS FIELD, '888' AS EXPECTED, '888' AS ACTUAL, 'NA' AS ACTION FROM DUAL UNION ALL
SELECT '123456789' AS PERSON_ID, '2' AS LICENSE_ID, 'ISSUE_DT' AS FIELD, '43498' AS EXPECTED, '' AS ACTUAL, 'UPDATE' AS ACTION FROM DUAL

The table looks like this:

The output I need is like this:

Gordon Linoff · Accepted Answer · 2019-09-21 17:18:53Z

1

You can use conditional aggregation:

select person_id, licence_id,
       max(case when field = 'NUMBER' then expected end) as expected_number,
       max(case when field = 'NUMBER' then actual end) as actual_number,
       max(case when field = 'NUMBER' then actual end) as actual_number,
       max(case when field = 'ISSUE_DT' then expected end) as expected_issue_dt,
       max(case when field = 'ISSUE_DT' then actual end) as actual_issue_dt,
       max(case when field = 'ISSUE_DT' then action end) as actual_issue_dt_action,
from t
group by person_id, licence_id

answered Sep 21, 2019 at 17:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL Pivot Multiple Columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related