0

Below the table as I have it. I have an alias column with a value. I'm trying to make dynamic columns from the alias. The aliases are dynamic so they can change.

MySQL Join Multiple Rows as Columns here they have 2 tables that they join, I have only one. But still I can't get it to work. I wonder if I really need a prepare statement.

When I use 

SELECT GROUP_CONCAT(DISTINCT CONCAT('value AS ', alias))

I'm get that piece of query as column name.

ID   value    alias
1    aaa      test1
1    bbb      test2
1    ccc      test3
2    ddd      test1
2    eee      test2
2    fff      test3

The desired result right from the MySQL query is:

ID    test1    test2    test3
1     aaa      bbb      ccc
2     ddd      eee      fff

Does anyone know how to accomplish this?

Community
  • 1
  • 1
Tim van Uum
  • 1,873
  • 1
  • 17
  • 36
  • 4
    Possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) – Shadow Sep 19 '16 at 14:34
  • This question has been asked numerous times. – spencer7593 Sep 19 '16 at 14:37

1 Answers1

0

A simple pivot query should work here:

SELECT ID,
       MAX(CASE WHEN alias = 'test1' THEN value ELSE NULL END) AS test1,
       MAX(CASE WHEN alias = 'test2' THEN value ELSE NULL END) AS test2,
       MAX(CASE WHEN alias = 'test3' THEN value ELSE NULL END) AS test3
FROM yourTable
GROUP BY ID
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thank you for your answer. This is cool, but the aliases are dynamic. I'll edit my question. – Tim van Uum Sep 19 '16 at 14:21
  • @Tim van Uum: The statement in this answer will produce the resultset. If you want this to be dynamic, to have a varying number of columns, or different column aliases, that can't be done dynamically within a single SQL statement. The dynamic part of the process, a separate step, produces this SQL text to be executed. – spencer7593 Sep 19 '16 at 14:35