请各位SQL大牛帮忙看看SQL动态行列转换疑难问题：动态行转列后，超过动态第10列(即：行转列动态大于第10列时，如何又从动态1开始折行显示？谢谢.
下面是测试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'
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 by d.ordeno,d.pnno,d.dsc'
exec (@sql)
------解决思路----------------------
--借助一临时表
select *,rn=ROW_NUMBER() over (partition by pnno,(rn1-1)/10 order by codeno) into #T from
(select *,rn1=row_number() over(partition by pnno order by codeno) from test) a
select * from #T
Declare @s varchar(max)
select @s=ISNULL(@s+',','')+'MAX(case when rn='''+convert(varchar,rn)+''' then qty end) as ['+'数'+convert(varchar,rn)+']' from #T group by rn
set @s='select ordeno as 订单,pnno as 品号,dsc as 名称,'+@s+' from #T group by ordeno ,pnno,dsc,(rn1-1)/10'
print @s
exec(@S)
drop table #T
------解决思路----------------------
--修改一下原id的算法，再加个id2用作分组：
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 (select a.*,id=(ROW_NUMBER()over(partition by pnno order by pnno desc)-1)%10+1,id2=(ROW_NUMBER()over(partition by pnno order by pnno desc)-1)/10 from test a)d group by d.ordeno,d.pnno,d.dsc,id2'
exec (@sql)