CODEWITHSUNDEEP
mysql
Nimatullah Razmjo
Nimatullah Razmjo

Reputation: 1961

convert dynamically the rows to columns with out specifying fields

How can I convert dynamically a key-value table using Case without specifying row names?
For example I have this table named key_value:

id     key     value
1     name     john
2     fname    akbar
3     jobs     Software enginer
.    .    .
.    .    .
.    .    .
.    .    .
.    .    .
n    n    n

I want to convert all these rows dynamically to columns without specifying key name like:

name   fname   jobs............................n
john    akbar   sofware engineer...........n

I have used:

Max(Case WHEN key='name' THEN value END) AS name

In this query I know my key.
What if I don't know my fields and I don't know how many fields I have?

I want to convert all of this dynamically without specifying my fields.

Upvotes: 2

Views: 197

Answers (1)

Ruben_PH
Ruben_PH

Reputation: 1812

This was also my question way back
SQL Query fields as columns
I modified it to answer yours and I hope it helps just like it did to me!

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(a.xvalue = ''',
      xvalue,
      ''', a.xvalue, NULL)) AS ',
      xkey
    )
  ) INTO @sql
FROM key_value;


SET @sql = CONCAT('SELECT ', @sql, ' 
                   FROM key_value a
                   LEFT JOIN key_value AS b
                    ON a.id=b.id');

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

SQL Fiddle

Upvotes: 5

Related Questions