动态行转列且一行转多列

if object_id('tempdb..#test') is not null
drop table #test
create table #test
( name nvarchar(200),
orderdate varchar(7),
ordernum int ,
comnum int
)

insert into #test
select 'AAA','2019-01',77,77
union all
select 'AAA','2019-02',66,77
union all
select 'BBB','2019-03',96,86
union all
select 'AAA','2019-04',88,89
union all
select 'BBB','2019-04',120,115
select *from #test
declare @event varchar(3000)='',@sql varchar(3000)='',@queryexp varchar(3000)=''
if object_id('tempdb..#hbTab') is not null
drop table #hbtab
select name,(orderdate+'_shouli') as [year],ordernum,orderdate into #hbtab from #test
select * from #hbtab
--列转行
insert into #hbtab
select name,(orderdate+'_banjie') as [year],comnum,orderdate from #test
select * from #hbtab
--获取行的属性
--select *from #hbtab
select @event=@event+',['+[year]+']' from (select distinct [year] from #hbtab) a order by [year]
print @event
select @queryexp=@queryexp+',max(['+[year]+']) as '+'['+[year]+']' from (select distinct [year] from #hbtab) a order by [year]
print @queryexp
select @queryexp=right(@queryexp,len(@queryexp)-1)
print @queryexp
select @event=right(@event,len(@event)-1)
print @event

--select *from #hbtab order by year
set @sql='select name,'+@queryexp+' from( select name,'+@event +'from #hbtab a
pivot (max(ordernum) for year in('+@event+')
) as pv ) b group by name'
print @sql
exec(@sql)