1

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.

YNR
  • 867
  • 2
  • 13
  • 28

1 Answers1

3
select id2, time,
       sum(case when id3 = 0 then val else 0 end) as col0,
       sum(case when id3 = 1 then val else 0 end) as col1,
       sum(case when id3 = 2 then val else 0 end) as col2,
       sum(case when id3 = 3 then val else 0 end) as col3,
       sum(case when id3 = 4 then val else 0 end) as col4
from your_table
group by id2, time
having sum(val) > 0
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Thank you. I think after 'then', 'id3' should be replaced by 'Val''. If we set a condition that remove the rows that have zero values for col0 to col4 it would be better. Also, I'm wondering how can I optimize this query to reduce time complexity. – YNR Sep 29 '16 at 12:30
  • You are right, I changed `id3` to `val`. Also I added a `having` clause to remove those `0` results. – juergen d Sep 29 '16 at 14:33