Join multiple rows in SQL tables based one column value

Question

I have a SQL table defined like so:

 id    time    data
  1     1       11
  1     2       12
  1     3       13
  2     1       21
  2     2       22
  2     3       23
  3     1       31
  3     2       32
  3     3       33

I would like to create a new table that combines all the rows with the same time value like so:

time    data   data   data
 1       11     21     31
 2       12     22     32
 3       13     23     33

I have tried selecting the time on based on time, but I get a lot of duplicate rows with different column orders. What is the best way to do this in SQL

Answer 1

You can do:

select
  time,
  sum(case when id = 1 then data end) as data,
  sum(case when id = 2 then data end) as data,
  sum(case when id = 3 then data end) as data
from t
group by time