How can I do this with MySQL?

Question

I am using MySQL and have two database tables as follows:

Users

id   username
--------------
1    Bill
2    Steve

Objects

user_id   key   value
----------------------
1         A     X
1         B     Y
1         C     Z
2         A     S
2         C     T

What query is required to produce the following result?

username   A  B  C
-------------------
Bill       X  Y  Z
Steve      S     T

I have tried this with an INNER JOIN, but end up with 5 rows (one for each corresponding object row).

Any help much appreciated.

http://stackoverflow.com/questions/6605604/mysql-pivot-query-results-with-group-by — Benjam, Dec 02 '11 at 21:58
possible duplicate of [Can a MySQL query turn rows into columns?](http://stackoverflow.com/questions/4336985/can-a-mysql-query-turn-rows-into-columns) — Ben Lee, Dec 02 '11 at 22:00
here is one way [http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html](http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html) — Saic Siquot, Dec 02 '11 at 21:54

score 3 · Answer 1 · answered Dec 02 '11 at 22:07

If 'A', 'B', and 'C' are known beforehand, you can do this:

SELECT users.username,
       ( SELECT objects.value
           FROM objects
          WHERE objects.user_id = users.id
            AND objects.`key` = 'A'
       ) AS a,
       ( SELECT objects.value
           FROM objects
          WHERE objects.user_id = users.id
            AND objects.`key` = 'B'
       ) AS b,
       ( SELECT objects.value
           FROM objects
          WHERE objects.user_id = users.id
            AND objects.`key` = 'C'
       ) AS c
  FROM users
 ORDER
    BY users.username
;

score 2 · Accepted Answer · answered Dec 02 '11 at 22:19

2

select u.username
     , oA.value A
     , oB.value B
     , oC.value C
  from users u
left
  join objects oA
    on u.id = oA.user_id
   and oA.key = 'A'
left
  join objects oB
    on u.id = oB.user_id
   and oB.key = 'B'
left
  join objects oC
    on u.id = oC.user_id
   and oC.key = 'C'

answered Dec 02 '11 at 22:19

goat

31,486
7
73
96

this needs @ruakh warning too: If 'A', 'B', and 'C' are known beforehand – Saic Siquot Dec 02 '11 at 22:48
That worked perfectly, thank you. It was also faster than the sub-query method posted. – gjb Dec 03 '11 at 00:16

score 0 · Answer 3 · answered Dec 02 '11 at 22:15

perhaps this is not the query that you are asking for, but this is a clean and sipmle query that I have used in your situation:

select objects.*, matrix.*
from 
        (select users.id, o.key
          from users, (select distinct key from objects) as o
        ) 
as matrix left join objects on matrix.id = objects.user_id
                           and matrix.key = objets.key
order by matrix.id, matrix.key

This query "fills" the empty spaces. So you can consume the resultset with two nested foreach (or whatever similar) and draw the desired table.

How can I do this with MySQL?

3 Answers3