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 ?
This is done with a pivot table. Grouping by
id
, you issueCASE
statements for each value you want to capture in a column and use something like aMAX()
aggregate to eliminate the nulls and collapse down to one row.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.What you are attempting to do is a
PIVOT
MySQL does not have aPIVOT
function so you can replicate this using aCASE
and an aggregate function.If you have a known number of columns, then you can use a static version and hard-code the values. Similar to this (See SQL Fiddle with demo):
But if you have an unknown number of columns, then you can use a prepared statement and create this dynamically:
See SQL Fiddle with Demo