9

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Jack
  • 2,206
  • 3
  • 18
  • 25
  • 5 questions, no accepted answer, 0 answers to other people's questions. You do know this is a community driven site and not somecompany's support forum, right? – Anax Jun 25 '10 at 22:59

3 Answers3

14

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
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • This is the answer i found most useful. I had a huge table to deal with, and this performed adequately. – Jack Jul 02 '10 at 22:03
  • Great, this is what i was looking for, for one of my SQLs. Thanks! – hese Jun 02 '11 at 15:27
  • Fantasic, modified this part: MAX(CASE WHEN d.code = 5 THEN d.value ELSE NULL END) to return a bool value to determine if a user's category exactly matched a given search category or if the two categories simply shared a common parent category. – nealio82 Aug 22 '12 at 08:03
  • Thanks for this, the use of MAX was what finally helped me get this, query below: `SELECT v.id, v.first, v.last, v.email, MAX(case vs.sid when '1' then 'Yes' else 'No' end) FB, MAX(case vs.sid when '2' then 'Yes' else 'No' end) Twitter, MAX(case vs.sid when '3' then 'Yes' else 'No' end) Instagram, MAX(case vs.sid when '4' then 'Yes' else 'No' end) Vine, MAX(case vs.sid when '5' then 'Yes' else 'No' end) Google+ FROM t1 v LEFT OUTER JOIN t2 u ON u.id = v.id LEFT OUTER JOIN t3 vs ON vs.id = v.id WHERE v.test=0 AND v.help=1 AND u.activationstate='Active' GROUP BY v.id` – nbsp Aug 12 '13 at 03:23
  • This worked perfect, I was trying a group coalesce into a column with keys and values but it varied, threw it under a single column, and wasn't feasible. – CTS_AE Feb 20 '14 at 00:59
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
Imre L
  • 6,159
  • 24
  • 32
  • Thanks! But i actually needed 3 values, and i modified the above in the "obvious" way (adding another join), and that worked; but now the problem i'm having is that if any user is missing any of the rows (eg, has A and B, but is missing C), then i end up with no row for that user. Instead, i'd like to see a row with 0's for any missing values from the details table. Any ideas on that? – Jack Jun 25 '10 at 23:29
0

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).

Jason McCreary
  • 71,546
  • 23
  • 135
  • 174