动态SQL进阶

--创建测试数据

if object_id('test') is not null

drop table test

create table test(ordeno char(10),codeno varchar(10),pnno char(20),dsc char(20),qty int,remark varchar(20),field1 varchar(20))

insert into test

select 'C001','0001','123','123',500,'','TEST' union all

select 'C001','0002','123','123',1000,'','TEST1' union all

select 'C001','0003','123','123',1500,'','TEST2' union all

select 'C001','0004','123','123',2000,'','TEST' union all

select 'C001','0005','123','123',2500,'','TEST' union all

select 'C001','0006','123','123',3000,'','TEST2' union all

select 'C001','0007','123','123',3500,'','TEST' union all

select 'C001','0008','456','456',500,'','TEST' union all

select 'C001','0009','456','456',700,'','TEST2' union all

select 'C001','0010','456','456',1000,'','TEST' union all

select 'C001','0011','456','456',1100,'','TEST' union all

select 'C001','0012','456','456',1500,'','TEST2' union all

select 'C001','0013','456','456',1800,'','TEST' union all

select 'C001','0014','456','456',1900,'','TEST1' union all

select 'C001','0015','456','456',2000,'','TEST' union all

select 'C001','0016','456','456',2500,'','TEST2' union all

select 'C001','0017','456','456',3000,'','TEST' union all

select 'C001','0018','456','456',5500,'','TEST'

go

--返回数据

select from test

--我佛慈悲

declare @sql varchar(8000)

set @sql='select ordeno as ''订单'',pnno as ''品号'',dsc as ''名称'''

select @sql=@sql+',max(case d.id when '''+c.id+''' then d.qty else 0 end)as ['+''+id+']'

from (select cast(id as varchar(1000)) as id from (select a.,id=ROW_NUMBER()

over(partition by pnno order by pnno desc) from test a)b group by id) as c select @sql=@sql

+'from (select a.*,id=ROW_NUMBER()over(partition by pnno order by pnno desc) from test a)d group byd.ordeno,d.pnno,d.dsc'

exec (@sql)