Mysql split column into multiple columns

Question

I have a table like:

  id  |  cat  |  price
----------------------
  0   |   1   |  2
  0   |   2   |  1 
  0   |   1   |  1
  1   |   1   |  31
  1   |   2   |  5
----------------------

I like to select it like:

  id  |  cat1_price  |  cat2_price  
----------------------------------
  0   |       3      |      1
  1   |      31      |      5
----------------------------------

My query so far:

SELECT SUM(`price`) as cat1_price FROM price_table WHERE cat = 1 GROUP BY id

which works to get one of the needed columns. How can I have both?

I also tried something like:

 SELECT SUM(`price`) as cat1_price 
(SELECT SUM(`price`) FROM price_table WHERE cat = 2) as cat2_price
 FROM price_table WHERE cat = 1 GROUP BY id

which works too slow. The actual table is pretty big and has some some joins.

I'm not sql guru, so I hope there is a query for this I'm not aware of :)

Why do you want to do this? (the separate columns bit, I mean) — Strawberry
– Strawberry, Commented Sep 15, 2014 at 13:15
A sortable table? - you mean the kind of thing that's done with ajax and php? — Strawberry
– Strawberry, Commented Sep 15, 2014 at 14:03

Gordon Linoff · Accepted Answer · 2014-09-15 12:29:02Z

5

Just use conditional aggregation:

SELECT id, SUM(case when cat = 1 then `price` else 0 end) as cat1_price,
       SUM(case when cat = 2 then `price` else 0 end) as cat1_price
FROM price_table
GROUP BY id;

answered Sep 15, 2014 at 12:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql split column into multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related