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?

Solution 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.

Solution 2

What you are attempting to do is a PIVOT MySQL does not have a PIVOT function so you can replicate this using a CASE 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):

select id,
  max(case when col1='name' then col2 end) name,
  max(case when col1='name2' then col2 end) name2,
  max(case when col1='name3' then col2 end) name3
from yourtable
group by id

But if you have an unknown number of columns, then you can use a prepared statement and create this dynamically:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when col1 = ''',
      col1,
      ''' then col2 end) AS ',
      col1
    )
  ) INTO @sql
FROM yourtable;

SET @sql = CONCAT('SELECT id, ', @sql, ' 
                  FROM yourtable 
                  GROUP BY id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

Comments

[dburges]

It of course woudl be easier all around if you avoided storing column names as data and used a table where they were actual column names. Difficulty qquerying is one of the big disadvantages of using an EAV struture, along with performance. Do you really need that type of structure and can you redesign?

[ravi]

that was a very quick response and thank you very much .... that works like a charm and this is what i was expecting to do. Awesome ... I appreciate ur quick help.

[Michael Benjamin]

An explanation would be helpful. And consider using code formatting for improved readability.

[Pygirl]

How to do it via pivot operator?