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.

CodePudding user response：

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.