Pivot in Oracle based on multiple columns

Question

I have sample data like this in main table, value column has varchar as datatype.

|primary_id   |identifier_one   |identifier_two   |value      |
|-------------|-----------------|-----------------|-----------|
|10001        |3000             |23               |23_val_1   |
|10001        |3000             |24               |24_val_1   |
|10001        |3001             |25               |25_val_1   |
|10001        |3001             |26               |26_val_1   |
|10002        |3000             |23               |23_val_2   |
|10002        |3000             |24               |24_val_2   |
|10002        |3001             |25               |25_val_2   |
|10002        |3001             |26               |26_val_2   |

My desired output is

|primary_id   |3000_23_col     |3000_24_col     |3001_25_col     |3001_26_col     | 
|-------------|----------------|----------------|----------------|----------------|
|10001        |23_val_1        |24_val_1        |25_val_1        |26_val_1        |
|10002        |23_val_2        |24_val_2        |25_val_2        |26_val_2        |

I have to query main table and with where clause such as primary_id=1001, value is actually based on two columns identifier_one and identifier_two, also in resulting table column name would be different may use alias. I tried using pivot but couldn't form a proper query. Please guide me.

Del · Accepted Answer · 2021-10-04 18:23:07Z

2

You can definitely use multiple columns within a PIVOT clause, here is an example using your table setup:

SELECT *
FROM demo
PIVOT (MAX(VALUE) FOR (identifier_1, identifier_2) IN ((3000, 23) AS A3000_23, (3000, 24) AS A3000_24,
                                                        (3001, 25) AS A3001_25, (3001, 26) AS A3001_26));

N.B.: Excuse the "A" in the column names, you need to start the identifier with a character, not a number.

Here is a DBFiddle showing the results (LINK)

Obviously you can see how this would quickly grow out-of-hand if you need to list large amounts of PIVOT columns.

answered Oct 4, 2021 at 18:23

Del

1,6311 gold badge10 silver badges18 bronze badges

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

1 Comment

mrcrag Over a year ago

Hi Del, I'm struggling to make a double/double pivot. Do you know if that will work? PIVOT (MAX(VALUE), AVG(FIELDX) FOR (identifier_1, identifier_2) IN ((3000, 23) AS A3000_23...

Collectives™ on Stack Overflow

Pivot in Oracle based on multiple columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related