i have below table
name month year count ---------------------------- xxx 12 2012 24 xxx 1 2013 42 xxx 2 2013 23 yyy 12 2012 34 yyy 1 2013 12 yyy 2 2013 54 i convert below format,
name dec-12 jan-13 feb-13 -------------------------------- xxx 24 42 23 yyy 34 12 54 how apply pivot?
since using sql server there several ways can pivot data rows columns.
if values limited or have known number of values, can hard-code values static pivot:
select name, [dec_12], [jan_13], [feb_13] ( select name, left(datename(month, dateadd(month, month, 0) -1), 3) +'_'+right(cast(year varchar(4)), 2) my, [count] yourtable ) src pivot ( sum(count) in ([dec_12], [jan_13], [feb_13]) ) piv; see sql fiddle demo.
now, if have unknown number of values, need implement dynamic sql generate result:
declare @cols nvarchar(max), @query nvarchar(max) select @cols = stuff((select ',' + quotename(my) ( select left(datename(month, dateadd(month, month, 0) -1), 3) +'_'+right(cast(year varchar(4)), 2) my, cast( cast(year varchar(4)) + right('0' + cast(month varchar(2)), 2) + '01' datetime) fulldate yourtable ) t group my, fulldate order fulldate xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') set @query = 'select name, ' + @cols + ' ( select name, left(datename(month, dateadd(month, month, 0) -1), 3) +''_''+right(cast(year varchar(4)), 2) my, [count] yourtable ) x pivot ( sum(count) in (' + @cols + ') ) p ' execute(@query) see sql fiddle demo.
this difference , static version if need unknown number of dates or want automatically update new dates when available, return new data without changing code.
the result of both queries is:
| name | dec_12 | jan_13 | feb_13 | ----------------------------------- | xxx | 24 | 42 | 23 | | yyy | 34 | 12 | 54 | 
Comments
Post a Comment