declare
@set nvarchar(max),
@sql nvarchar(max)
declare
@tablenew table(a float,b float,c float,d float,e float,f float,g float,h float,k float,i float)
set @set=''
select @set=@set+'['+calendar.LedgerPeriod+'],' from
parameter cross join calendar
where
(parameter.year*100+parameter.month)<=calendar.ledgerPeriod
and
(
((parameter.year+1)*100+(parameter.month+9-12)>= calendar.ledgerPeriod)
)
if(len(@set)>1)
set @set=left(@set,len(@set)-1)
set @sql=
'
select * from
(
select
calendar.LedgerPeriod,
calendar.WeekNums from
parameter cross join calendar
where
(parameter.year*100+parameter.month)<=calendar.ledgerPeriod
and
(
((parameter.year+1)*100+(parameter.month+9-12)>= calendar.ledgerPeriod)
)
)as kkkkkk
pivot(sum(WeekNums) for LedgerPeriod
in('+@set+')) as mmm '
--exec(@sql)
insert into @tablenew exec(@sql)
select * from @tablenew