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
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.
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.
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 |
+----+---------------------+-----+------+------+
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With