sql server - Dynamic pivot table insert -

i have created dynamic pivot table concatenating column names , executing string. table results this

   productid          b      c     d 

in future additional field may entered (e. f... ect) hence need piviot query dynamic. need insert data existing table has columns z.

how can create insert query dynamic when new field added insert query doesn't need changed in code?

a stored proc (listed below) pivot data few options. can have multiple group by's. following take results #temp table , pivot based on parameters. should note. columns can expressions.

select year=year(tr_date),day=day(tr_date),month=right(concat('00',month(tr_date)),2),tr_y10 #temp  [chinrus-series].[dbo].[ds_treasury_rates] tr_date>='2000-01-01'  exec [prc-pivot] 'select * #temp','month','sum(tr_y10)[]','year,day' 

returns

year    day 01      02      03      04      05      06      07      08      09      10      11      12 2000    1   0.00    6.62    6.39    0.00    6.29    6.20    0.00    6.00    5.68    0.00    5.74    5.52 2000    2   0.00    6.60    6.40    0.00    6.32    6.15    0.00    5.98    0.00    5.83    5.74    0.00 2000    3   6.58    6.49    6.39    6.00    6.40    0.00    6.00    5.95    0.00    5.87    5.83    0.00 2000    4   6.49    6.53    0.00    5.90    6.46    0.00    0.00    5.91    0.00    5.90    0.00    5.53 2000    5   6.62    0.00    0.00    5.90    6.51    6.12    5.99    0.00    5.69    5.87    0.00    5.43 2000    6   6.57    0.00    6.42    5.93    0.00    6.14    6.05    0.00    5.72    5.82    5.87    5.32 2000    7   6.52    6.64    6.39    5.86    0.00    6.13    6.01    5.97    5.76    0.00    5.87    5.32 2000    8   0.00    6.59    6.38    0.00    6.57    6.13    0.00    5.93    5.73    0.00    5.87    5.35 2000    9   0.00    6.56    6.35    0.00    6.53    6.13    0.00    5.81    0.00    0.00    5.82    0.00 2000    10  6.57    6.67    6.39    5.80    6.47    0.00    6.04    5.76    0.00    5.80    5.82    0.00 2000    11  6.67    6.63    0.00    5.89    6.43    0.00    6.06    5.79    5.77    5.77    0.00    5.37 2000    12  6.72    0.00    0.00    5.97    6.51    6.09    6.09    0.00    5.78    5.73    0.00    5.36 2000    13  6.63    0.00    6.36    5.94    0.00    6.11    6.01    0.00    5.74    5.73    5.77    5.29 

... (527 rows in total)

i should add, if destination table has fixed structure, can

insert yourtablename  exec [prc-pivot] 'select * #temp','month','sum(tr_y10)[-optionalsuffix]','year,day' 

the stored procedure:

create procedure [dbo].[prc-pivot] (@select varchar(1000),     @pvotcol varchar(100),     @summaries varchar(100),     @groupby varchar(100),     @othercols varchar(100) = null)  set nocount on set ansi_warnings off  declare @vals varchar(max); set @vals = '' set @othercols= isnull(', ' + @othercols,'') create table #temppvot  (pvot varchar(100)) insert #temppvot exec ('select distinct convert(varchar(100),' + @pvotcol + ') pvot (' + @select + ') a') select @vals = @vals + ', isnull(' + replace(replace(@summaries,'(','(case when ' + @pvotcol + '=''' + pvot +  ''' '),')[', ' end),0) [' + pvot ) #temppvot order pvot drop table #temppvot exec ('select ' + @groupby + @othercols + @vals + ' (' + @select + ') group ' + @groupby + ' order ' + @groupby)  set nocount off set ansi_warnings on 

some extended options:

exec [prc-pivot] 'select * #temp','month','avg(tr_y10)[-month]','year','min(tr_y10)[min],max(tr_y10)[max],avg(tr_y10)[avg],cast(stdev(tr_y10) money)[stddev]'   year    min max avg stddev  01-month    02-month    03-month    04-month    05-month    06-month    07-month    08-month    09-month    10-month    11-month    12-month 2000    5.02    6.79    6.0302  0.3919  6.661   6.5195  6.2565  5.9905  6.4404  6.0972  6.054   5.826   5.799   5.7385  5.7171  5.2405 2001    4.22    5.54    5.0206  0.2857  5.1609  5.0989  4.8854  5.141   5.3913  5.2842  5.2361  4.9713  4.7317  4.5668  4.6515  5.0875