Setting column values as column names in the SQL query result

Question

I wanted to read a table which has values which will be the column names of the sql query result. For example, I have table1 as ..

id    col1     col2
----------------------
0      name    ax
0      name2   bx
0      name3   cx
1      name    dx
1      name2   ex
1      name3   fx                

If you see for id = 0, name has value of ax and name2 is bx and name3 is cx. Instead of this being rows it would be easier to show columns as id, name, name2, name3. Now I want the result of the query to look like this:

id   name    name2     name3
0    ax      bx         cx
1    dx      ex         fx

Can someone help me in achieving this?

Answer 1

This is done with a pivot table. Grouping by id, you issue CASE statements for each value you want to capture in a column and use something like a MAX() aggregate to eliminate the nulls and collapse down to one row.

SELECT
  id,
  /* if col1 matches the name string of this CASE, return col2, otherwise return NULL */
  /* Then, the outer MAX() aggregate will eliminate all NULLs and collapse it down to one row per id */
  MAX(CASE WHEN (col1 = 'name') THEN col2 ELSE NULL END) AS name,
  MAX(CASE WHEN (col1 = 'name2') THEN col2 ELSE NULL END) AS name2,
  MAX(CASE WHEN (col1 = 'name3') THEN col2 ELSE NULL END) AS name3
FROM
  yourtable
GROUP BY id
ORDER BY id

Here's a working sample

Note: This only works as is for a finite and known number of possible values for col1. If the number of possible values is unknown, you need to build the SQL statement dynamically in a loop.