T-SQL PIVOT data from long form to wide by a date

Question

I want to pivot my data from long to wide using t-sql pivot function if possible. My current table looks like:

ID  DATE        NUM VALUE
1   2005-01-20  29  197
1   2005-01-20  28  58
1   2005-01-20  30  90
1   2005-02-08  29  210
1   2005-02-08  30  133
1   2005-02-08  28  67
2   2005-01-10  28  87
2   2005-01-10  30  119
2   2005-07-11  28  77
2   2005-07-11  29  174

Output should look like this:

ID  DATE        V28 V29  V30
1   2005-01-20  58  197  90
1   2005-02-08  67  210  133
2   2005-01-10  87  NULL 119
2   2005-07-11  77  74   NULL

Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — M--, Apr 29 '19 at 17:51

SqlZim · Accepted Answer · 2017-02-27T22:47:42.970

The traditional cross tab / conditional aggregation version of a pivot() would be like so:

select 
    id
  , date
  , v28 = sum(case when num = 28 then value end)
  , v29 = sum(case when num = 29 then value end)
  , v30 = sum(case when num = 30 then value end)
from t
group by id, date

pivot() version:

select
    Id
  , date
  , v28
  , v29
  , v30
from 
  (select id, date, num = 'v'+convert(varchar(10),num), value
    from t) as t
  pivot (sum(value) for num in (v28, v29, v30)) pvt

dynamic pivot code generation:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
  select @cols = stuff((
    select distinct 
      ', ' + 'v'+convert(varchar(10),num)
      from t 
      order by 1
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,2,'')
select  @sql = '
 select Id, date, ' + @cols + '
  from  (
    select Id, date, num = ''v''+convert(varchar(10),num), value
      from t
      ) as t
 pivot (sum([value]) for [num] in (' + @cols + ') ) p'
select @sql
exec(@sql);

generates the following:

select Id, date, v28, v29, v30
  from  (
    select Id, date, num = 'v'+convert(varchar(10),num), value
      from t
      ) as t
 pivot (sum([value]) for [num] in (v28, v29, v30) ) p

test them all here: http://rextester.com/ZJS18834

results (ordered by id, date)

+----+---------------------+-----+------+------+
| id |        date         | v28 | v29  | v30  |
+----+---------------------+-----+------+------+
|  1 | 20.01.2005 00:00:00 |  58 | 197  | 90   |
|  1 | 08.02.2005 00:00:00 |  67 | 210  | 133  |
|  2 | 10.01.2005 00:00:00 |  87 | NULL | 119  |
|  2 | 11.07.2005 00:00:00 |  77 | 174  | NULL |
+----+---------------------+-----+------+------+

T-SQL PIVOT data from long form to wide by a date

1 Answers1

Linked