java 动态行转列_动态行转列且一行转多列

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)