sql server - How can I a pivoted procedure be called as a sql query? -

i have procedure generates pivot below (see output) based on parameters passed it.

i want able insert columns in between years show in expected output add 100 pivoted values in new column.

is there way call pivot proc query can add calculations via select query? or there easier way?

create table t1 (     date int,     unita int,     unitb int,     unitc int )  insert t1 values (2010, 335, 52, 540) insert t1 values (2011, 384, 70, 556) insert t1 values (2012, 145, 54, 345)   select *  (     select date, value, unit          (         select *         t1     ) x     unpivot ([value] unit in ([unita], [unitb], [unitc])) u ) pivot (     sum(value)     date in ([2010], [2011], [2012]) ) p 

output:

unit    2010 2011 2012 ---------------------- unita   335  384  145 unitb   52   70   54 unitc   540  556  345 

expected output:

unit    2010 2010a 2011 2011a  2012 ----------------------------------- unita   335  435   384  485    145 unitb   52   150   70   170    54 unitc   540  640   556  656    345 

i don't think there's 'easy' way add columns result of pivot. can't away without dynamic sql in case. so, here's 1 of possible solutions. i've placed explanations in comments.

declare @dates table ([date] varchar(4)) declare @pivotcolumns varchar(500) declare @query nvarchar(max)  -- first, need construct list of values need pivot - `[2010], [2010a], [2011], [2011a], [2012]`. set @pivotcolumns = ''  insert @dates select distinct [date] t1  select     @pivotcolumns = @pivotcolumns + '[' + cast([date] varchar(4)) + ']' +         case             when [date] < (select max([date]) @dates) + ',[' + cast([date] varchar(4)) + 'a]'             else ''         end + ',' @dates order [date]  set @pivotcolumns = left(@pivotcolumns, len(@pivotcolumns) - 1)  -- second - in innermost query need data these columns in corresponding rows before unpivot. -- union main query appending 'a' 'date' values ,  -- incrementing values in unita, unitb, unitc columns 100 each row  -- except rows maximum 'date' value. -- third - use our @pivotcolumns variable pivot columns list. that's -- dynamic query here for.  set @query =  'select *  (     select [date], value, unit          (         select cast(date varchar(5)) [date], unita, unitb, unitc t1         union         select cast(date varchar(5)) + ''a'', unita + 100, unitb + 100, unitc + 100 t1         [date] < (select max([date]) t1)     ) x     unpivot ([value] unit in ([unita], [unitb], [unitc])) u ) pivot (     sum(value)     date in (' + @pivotcolumns + ') ) p ' -- execute query. exec sp_executesql @query