SQL Server Pivot with Dynamic Fields -

SQL Server Pivot with Dynamic Fields -

trying figure out best way pivot table dynamically on date field in next table. issue these dates alter cant hard code them in 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 output 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 using sql server, can utilize pivot function.

if values known, can hard-code values:

select * ( select id, value, date yourtable ) src pivot ( max(value) date in ([2012-12-29], [2012-10-29], [2013-02-02]) ) piv

see sql fiddle demo.

but if unknown, need utilize dynamic sql:

declare @cols nvarchar(max), @query nvarchar(max) select @cols = stuff((select distinct ',' + quotename(convert(varchar(50), date, 120)) yourtable xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') set @query = 'select id, ' + @cols + ' ( select id, value, convert(varchar(50), date, 120) date yourtable ) x pivot ( max(value) date in (' + @cols + ') ) p ' execute(@query)

see sql fiddle 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) |

sql-server pivot