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.

Answer 1

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.

Answer 2

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:

• pivot in mysql queries
• MySQL Query and Pivot Tables