In MySQL, How do you dynamically SELECT the values of one table, as columns in another?

Question

I'm no MySQL guru, but I get around the basic stuff pretty well. Thanks for your feedback.

I have two tables user and favorite. Each user can have multiple unique favorites.

table user u

[ user_id + name  ]
  100     | Sally

table favorite fav

[ fav_id  + user_id +  fav_key  +  fav_val ]
  1       | 100     | icecream  |    mint
  2       | 100     | candybar  |  snickers
  3       | 100     | color     |    red

I want to create a SELECT statement that will turn the user's favorites into columns using the fav_key value as the column header. *The problem is I will never know what the fav_val value will be as these are user entered, so the column names have to be generated dynamically.

SELECT ...

[ user_id + name  + fav_icecream + fav_candybar + fav_color ]
  100     | Sally |    mint      |   snickers   |   red

With some distant thought of performance in mind -- one of the issues is that I don't want to run two SELECT statements to get the user data and the user favorites (plus I like the idea of having the columns dynamically named in this way).

UPDATE

So this is called, pivoting, excellent.

What if I don't ever know what the values are? I need to dynamically generate the columns from a query on favorites.

possible duplicate of [pivot in mysql queries](http://stackoverflow.com/questions/6133990/pivot-in-mysql-queries) — Lukas Eder, Feb 14 '12 at 15:29
@Lukas_Eder I didn't know this was called pivoting. I guess if I knew what keyword to look up then that mightve been helpful. However I think this question is still useful to people not knowing what to search for — qodeninja, Feb 14 '12 at 15:31

score 4 · Accepted Answer · edited Jan 18 '21 at 12:30

4

Like this:

Select fav.user_id, u.name 
       MAX(case WHEN fav.fav_key = 'icecream' then fav.fav_val end) as 'fav_icecream',
       MAX(case WHEN fav.fav_key = 'candybar'  then fav.fav_val end) as 'fav_candybar',
       MAX(case WHEN fav.fav_key = 'color'    then fav.fav_val end) as 'fav_color'
From favorite fav
     inner join users u on fav.user_id = u.user_id
group by fav.user_id

Working Example: DEMO

Note that: the demo is for MS SQL Server but it is working the same way for mysql, I just tried to give you a live demo.

edited Jan 18 '21 at 12:30

Community

1
1

answered Feb 14 '12 at 15:31

Mahmoud Gamal

78,257
17
139
164

the problem is I'm not going to know the full list of favorites, this only works if i know what the values are. – qodeninja Feb 14 '12 at 15:38
@MGQ nice demo! This is the first time I've gotten a code demo on my answer and I didnt even know you could do this +1 ... still need the dynamically generated columns though =] – qodeninja Feb 14 '12 at 15:44
1

@qodeninja, This is a popular problem that (in MS SQL Server) there is a workarounds, and for mysql there should be workarounds as well try searching for *Mysql PIVOT Table with dynamic columns* see this post for example in SO here: http://stackoverflow.com/questions/6158230/mysql-pivot-table-with-dynamic-headers-based-on-single-column-data – Mahmoud Gamal Feb 14 '12 at 15:52

score 3 · Answer 2 · edited May 23 '17 at 10:34

3

Essentially you want PIVOT functionality. Unfortunately, that's not natively supported by MySQL (unlike Oracle or SQL Server). There are many questions on StackOverflow showing how to work around that lacking functionality of MySQL:

https://stackoverflow.com/search?q=mysql+pivot+sql

Some examples:

edited May 23 '17 at 10:34

Community

1
1

answered Feb 14 '12 at 15:30

Lukas Eder

211,314
129
689
1,509

In MySQL, How do you dynamically SELECT the values of one table, as columns in another?

2 Answers2