高难度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 'A001','0001','123','123',500,'','AA' union all

select 'A001','0002','123','123',1000,'','AA1' union all

select 'A001','0003','123','123',1500,'','AA2' union all

select 'A001','0004','123','123',2000,'','AA' union all

select 'A001','0005','123','123',2500,'','AA' union all

select 'A001','0006','123','123',3000,'','AA2' union all

select 'A001','0007','123','123',3500,'','AA' union all

select 'A001','0008','456','456',500,'','AA' union all

select 'A001','0009','456','456',700,'','AA2' union all

select 'A001','0010','456','456',1000,'','AA' union all

select 'A001','0011','456','456',1100,'','AA' union all

select 'A001','0012','456','456',1500,'','AA2' union all

select 'A001','0013','456','456',1800,'','AA' union all

select 'A001','0014','456','456',1900,'','AA1' union all

select 'A001','0015','456','456',2000,'','AA' union all

select 'A001','0016','456','456',2500,'','AA2' union all

select 'A001','0017','456','456',3000,'','AA' union all

select 'A001','0018','456','456',5500,'','AA' union all

select 'A001','0019','456','456',6000,'','AA' union all

select 'A001','0020','456','456',6800,'','AA' union all

select 'A001','0021','789','789',8888,'','CC'

go

--返回数据

select from test

--动态转

/

declare @sqlvarchar(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 (selectcast(id as varchar(1000)) as id from (select a.,id=ROW_NUMBER()

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

+'from (selecta.,id=ROW_NUMBER() over(partition by pnno order by pnno desc) from test a)dgroup by d.ordeno,d.pnno,d.dsc'

exec (@sql)

/

--我佛慈悲

declare @rows int

set @rows=6

select ,rn=row_number() over(partition by pnno,(rn1-1)/@rows order by codeno) into #t

from (select ,rn1=row_number() over(partition by pnno order by codeno) from test) a

--动态生

declare @rowno int,@sql varchar(max)

set @rowno=6

select @sql=isnull(@sql+',','')+

'max(case whenrn='''+convert(varchar,rn)+''' then qty else 0 end) as ['+''+convert(varchar,rn)+']'

from #t group by rn set @sql='select ordeno as 订单,pnno as 品号,dsc as 名称,

'+@sql+' from #t group byordeno,pnno,dsc,(rn1-1)/'+convert(char(1),@rowno)+''

print @sql

exec(@sql)

--删除实例

drop table #t



--修改算法1

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)-1)%10+1 from test a)b group by id) as c select @sql=@sql

+'from (selecta.,id=(ROW_NUMBER()over(partition by pnno order by pnno desc)-1)%10+1,

id2=(ROW_NUMBER()over(partitionby pnno order by pnno desc)-1)/10 from test a)d group byd.ordeno,d.pnno,d.dsc,id2'

exec (@sql)



--修改算法2

declare @rowno int,@sql varchar(max)

set @rowno=10

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)-1)%@rowno+1 from test a)b group by id) as c select @sql=@sql

+'from (selecta.,id=(ROW_NUMBER()over(partition by pnno order by pnno desc)-1)%@rowno+1,

id2=(ROW_NUMBER()over(partitionby pnno order by pnno desc)-1)/'+convert(char(2),@rowno)+' from test a)d group byd.ordeno,d.pnno,d.dsc,id2'

exec (@sql) 

 /

select id,casewhen idx=0 then 10 else idx end as idx from (

select cast(idas varchar(1000)) as id,

row_number()over (order by id) %10 as idx

from (selecta.,id=ROW_NUMBER()

over(partitionby pnno order by pnno desc) from test a)b group by id

) c

/