YNr YNr - 2 months ago 7
MySQL Question

MySQL GROUP BY a column and show values in different columns

I have a table as below. It is required for each distinct value of ID2, and Time to select rows that have ID3 in (0,1,2,3,4). Then, I want to show 'Val' in 5 columns, and fill the empty columns with zero.
(If ID3=0 then col0=Val
If ID3=1 then col1=Val, and ...)

ID1 ID2 ID3 Val Time
1 224 0 224 10
2 224 1 48 10
3 224 4 270 15
4 224 5 1000 27
5 225 0 231 10
6 225 1 400 10
7 225 0 100 20


In other words, the output should be as follow (group by ID2, Time ):

ID2 Time col0 col1 col2 col3 col4
224 10 224 48 0 0 0
224 15 0 0 0 0 270
225 10 231 400 0 0 0
225 20 100 0 0 0 0


Note: table has 50M rows.

Answer
select id2, time,
       sum(case when id3 = 0 then id3 else 0 end) as col0,
       sum(case when id3 = 1 then id3 else 0 end) as col1,
       sum(case when id3 = 2 then id3 else 0 end) as col2,
       sum(case when id3 = 3 then id3 else 0 end) as col3,
       sum(case when id3 = 4 then id3 else 0 end) as col4
from your_table
group by id2, time
Comments