ansaurus

Question

MySQL query help: how to pivot this table?

Answer 1

+1 A:

Try this, for MySQL (doesn't work in ANSI SQL):

SELECT  
  ID1,  
  GROUP_CONCAT(if(Name = 'item_name', Value, NULL)) AS 'item_name', 
  GROUP_CONCAT(if(Name = 'item_price', Value, NULL)) AS 'item_price', 
  GROUP_CONCAT(if(Name = 'animal_name', Value, NULL)) AS 'animal_name', 
  GROUP_CONCAT(if(Name = 'animal_name', Value, NULL)) AS 'animal_name', 
FROM tbl 
GROUP BY ID1, ID2, ID3;

(Disclaimer: untested with your data.)

chryss 2010-08-15 14:32:58

@chrss, got the same thing as Mark's suggestion: http://pastebin.com/u4fBLfWc. Any other ideas?

StackOverflowNewbie 2010-08-16 23:12:21

Answer 2

+1 A:

EDITED, following SONewbie's comments

Should be ANSI compliant:

SELECT  
  ID1,  
  MAX(CASE WHEN Name = 'item_name' THEN Value ELSE NULL END) AS item_name, 
  MAX(CASE WHEN Name = 'item_price' THEN Value ELSE NULL END) AS item_price, 
  MAX(CASE WHEN Name = 'animal_name' THEN Value ELSE NULL END) AS animal_name, 
  MAX(CASE WHEN Name = 'animal_gender' THEN Value ELSE NULL END) AS animal_gender
FROM tbl 
GROUP BY ID1, ID2;

This won't produce exactly the output desired - instead, the output will look like this:

==============================================================
| ID1 | item_name | item_price | animal_name | animal_gender |
==============================================================
| 16  | Toys      | 55         | dog         | male          |
| 18  | Toys      | 55         | dog         | male          |
| 16  | Computer  | 200        | cat         | female        |
| 18  | Computer  | 200        | cat         | female        |
--------------------------------------------------------------

This is because ID1 and ID2 link cat and female with Computer and 200 only, while the same fields link dog and male with Toys and 55 only. Grouping by ID3 introduces lots of NULLs, since ID1 and ID3 together uniquely identify the data rows.

Mark Bannister 2010-08-16 10:59:39

@Mark, I'm getting NULLs in some cells. See: http://pastebin.com/u4fBLfWc. Any other ideas?

StackOverflowNewbie 2010-08-16 23:10:30

@SONewbie, I've edited my query slightly so it doesn't produce NULLs with the data you originally provided - as described in the edited answer, it isn't an exact match for your requirements (which can't be matched for the data supplied). Additionally, the data in the output in your pastebin.com link don't match the data supplied with the question.

Mark Bannister 2010-08-17 10:29:56

ansaurus

tags:

views:

answers:

MySQL query help: how to pivot this table?

related questions