Making columns from values - MySQL

Question

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?

Possible duplicate of MySQL pivot row into dynamic number of columns — Shadow
– Shadow, Commented Sep 19, 2016 at 14:34

Tim Biegeleisen · Accepted Answer · 2016-09-19 14:18:23Z

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

answered Sep 19, 2016 at 14:18

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim van Uum Over a year ago

Thank you for your answer. This is cool, but the aliases are dynamic. I'll edit my question.

spencer7593 Over a year ago

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

Collectives™ on Stack Overflow

Making columns from values - MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related