mysql converting multiple rows into columns in a single row
13,337
Solution 1
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
Solution 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
Author by
Jack
Updated on June 27, 2022Comments
-
Jack about 2 years
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?