Trying to figure out the best way to pivot a table dynamically on the date field in the following table. the issue is these dates change so I cant hard code them in the pivot statement.
id value date
1 55 2012-12-29 00:00:00:00
1 54 2012-10-29 00:00:00:00
1 89 2013-02-02 00:00:00:00
2 45 2012-12-29 00:00:00:00
2 54 2012-10-29 00:00:00:00
4 78 2012-12-29 00:00:00:00
4 90 2012-10-29 00:00:00:00
4 23 2012-10-29 00:00:00:00
I want the output to look like this
id 2012-12-29 00:00:00:00 2012-10-29 00:00:00:00 2013-02-02 00:00:00:00
1 55 54 89
2 45 54 null
4 78 90 23
Since you are using SQL Server, then you can use the PIVOT function.
If your values are known, then you can hard-code the values:
select *
from
(
select id, value, date
from yourtable
) src
pivot
(
max(value)
for date in ([2012-12-29], [2012-10-29], [2013-02-02])
) piv
See SQL Fiddle with Demo.
But if they are unknown, then you will need to use dynamic sql:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(varchar(50), date, 120))
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id, ' + @cols + ' from
(
select id, value, convert(varchar(50), date, 120) date
from yourtable
) x
pivot
(
max(value)
for date in (' + @cols + ')
) p '
execute(@query)
See SQL Fiddle with Demo.
The result of both queries is:
| ID | 2012-10-29 00:00:00 | 2012-12-29 00:00:00 | 2013-02-02 00:00:00 |
------------------------------------------------------------------------
| 1 | 54 | 55 | 89 |
| 2 | 54 | 45 | (null) |
| 4 | 90 | 78 | (null) |
try this
DECLARE @columns VARCHAR(8000)
-- set columns for pivot
SELECT @columns = COALESCE(@columns + ',[' + cast([date] as varchar(50)) + ']', '[' + cast([date] as varchar(50))+ ']')
FROM TableName
EXEC(' SELECT *, ' + @columns + ' FROM
(
SELECT * FROM TableName
) As a
PIVOT (MAX(value) FOR DATE IN (' + @columns + ') ) As P')
