Mysql convert multiple rows to columns in one row

Question

I have a table with columns:

• user_id int
• code int
• int value

And I want to create a pivot table that looks like this:

• user_id int
• valueA int
• valueB int

In the details table, the value A will correspond, for example, code 5, and the value B will correspond, for example, code 6, so I'm looking for something like:

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

The problem is that I am looking at several rows from the details table to populate one row in the summary table.

For example, if I had the following lines in detail:

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

In the summary table, I want to get the following:

 1 100 200 2 1000 2000 

Any ideas?

Answer 1

There is no PIVOT / UNPIVOT syntax in MySQL, which allows 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 

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: in the end, you will get several summary columns if the 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

If you have a managed set of codes (say, just 5 and 6), you can 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 change your JOIN depending on whether your codes are needed as a relationship from 1 to 1 (i.e. LEFT JOIN s).

If you have a large set of codes, I would look at a cursor that launches a similar query above for the set of results of your codes or uses another technology (i.e. PHP script).