mysql converting multiple rows into columns in a single row

Question

Hi, i have a details table with columns:

• user_id int
• code int
• value int

And i want to build a summary table that looks like:

• user_id int
• valueA int
• valueB int

In the details table, valueA would correspond to say, code 5, and valueB would correspond to say, code 6, so i'm looking for something like:

insert into summary (user_id,valueA,valueB) VALUES ( SELECT ??? from details );

The problem of course is that i'm looking at multiple rows from the "details" table to populate one row in the "summary" table.

Eg, if i had the following rows in details:

1  5  100
1  6  200
2  5  1000
2  6  2000

I want to end up with the following in the summary table:

1  100   200
2  1000  2000

Any ideas?

Answer 1

If you have a manageable set of codes (say just 5 and 6) you could do something like this:

SELECT details.user_id, code5.value, code6.value
FROM details JOIN
  (SELECT user_id, value FROM details WHERE code = 5) AS code5 USING(user_id)
  JOIN
  (SELECT user_id, value FROM details WHERE code = 6) AS code6 USING(user_id);

You may need to modify your JOINs depending on if your codes are not required as 1 to 1 relationship (i.e. LEFT JOINs).

If you have a large set of codes, I would look into a cursor runs a similar query above over a result set of your codes or using a different technology, (i.e. PHP script).

Answer 2

insert into summary (user_id,valueA,valueB) 
SELECT a.user_id, a.value, b.value 
from details a 
join details b on a.user_id = b.user_id 
WHERE a.code = 5 and b.code = 6;

beware: you will end up with multiple summary columns if user_id+code is not unique.

EDIT:

insert into summary (user_id,valueA,valueB) 
select u.user_id, ifnull(a.value,0), ifnull(b.value,0)
from (select distinct user_id from details /* where code in (5,6) */) u
left join details a on a.user_id = u.user_id and a.code = 5
left join details b on b.user_id = u.user_id and b.code = 6

Answer 3

MySQL doesn't have PIVOT/UNPIVOT syntax, which leaves you to use a combination of GROUP BY and CASE expressions:

INSERT INTO SUMMARY
  (user_id,valueA,valueB) 
  SELECT d.user_id,
         MAX(CASE WHEN d.code = 5 THEN d.value ELSE NULL END),
         MAX(CASE WHEN d.code = 6 THEN d.value ELSE NULL END),
    FROM DETAILS d
GROUP BY d.user_id