How to pivot several columns in Oracle SQL query

Data and Databases
1

Problem Description

I’m working with a dataset where information for one LICENSE_ID has been spread across multiple rows. My goal is to merge these rows so that I end up with one row for each PERSON_ID and LICENSE_ID pair.

Current Data Structure

Here’s a sample of my current data format:

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

Desired Output

I need to transform this data so that each unique combination of PERSON_ID and LICENSE_ID appears on a single row, with the FIELD values becoming separate columns. Each field should have its corresponding EXPECTED, ACTUAL, and ACTION values in dedicated columns.

What’s the best approach to achieve this pivot transformation in Oracle SQL?

2

conditional aggregation beats pivot syntax for readability. use case statements with max() like this: MAX(CASE WHEN FIELD='NUMBER' THEN EXPECTED END) AS NUMBER_EXPECTED - rinse and repeat for each field/column combo. don’t forget to group by person_id and license_id.

3

The tricky part here is pivoting multiple value columns at once. You need to pivot EXPECTED, ACTUAL, and ACTION values for each FIELD type, so you’ll want Oracle’s PIVOT clause with multiple aggregation functions.

Here’s what works:

SELECT PERSON_ID, LICENSE_ID,
       NUMBER_EXPECTED, NUMBER_ACTUAL, NUMBER_ACTION,
       ISSUE_DT_EXPECTED, ISSUE_DT_ACTUAL, ISSUE_DT_ACTION
FROM (
    SELECT PERSON_ID, LICENSE_ID, FIELD, EXPECTED, ACTUAL, ACTION
    FROM your_table_name
)
PIVOT (
    MAX(EXPECTED) AS EXPECTED,
    MAX(ACTUAL) AS ACTUAL, 
    MAX(ACTION) AS ACTION
    FOR FIELD IN ('NUMBER' AS NUMBER, 'ISSUE_DT' AS ISSUE_DT)
);

I’m using MAX for the aggregation since you’ve got one row per FIELD per LICENSE_ID - it just grabs that single value. This scales nicely if you add more field types later, and the column names stay clean and descriptive.

4

Nice approaches! How’s the performance between pivot vs conditional aggregation on bigger datasets? Have you tried LISTAGG to concat values first, then parse them out? Might be overkill but could handle different field types dynamically. What’s your dataset size looking like @CreativeChef89?