动态列 pivot 到表变量

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

转载于:https://www.cnblogs.com/honkcal/archive/2012/05/18/2507317.html