ravi ravi - 1 year ago 71
MySQL Question

setting column values as column names in the SQL query result

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 name2 fx

If u see for id = 0, name has value of ax and name 2 - bx and name3 = 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 Source

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.

  /* 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

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download