Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql-server

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
like image 987
G83 Avatar asked Feb 27 '17 22:02

G83


People also ask

How do I PIVOT data in SQL?

Introduction to SQL Server PIVOT operator You follow these steps to make a query a pivot table: First, select a base dataset for pivoting. Second, create a temporary result by using a derived table or common table expression (CTE) Third, apply the PIVOT operator.

How do I PIVOT a column into a row in SQL?

Then you apply the aggregate function sum() with the case statement to get the new columns for each color . The inner query with the UNPIVOT performs the same function as the UNION ALL . It takes the list of columns and turns it into rows, the PIVOT then performs the final transformation into columns.


1 Answers

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 |
+----+---------------------+-----+------+------+
like image 72
SqlZim Avatar answered Oct 01 '22 14:10

SqlZim