SQL Server：查询结果列数固定10列，其余换行显示

--测试数据
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

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
select * from test

select ordeno as 訂單,pnno as 品號,dsc as 名稱,MAX(case when rn='1' then qty end) as [?1],
	MAX(case when rn='2' then qty end) as [?2],MAX(case when rn='3' then qty end) as [?3],
	MAX(case when rn='4' then qty end) as [?4],MAX(case when rn='5' then qty end) as [?5],
	MAX(case when rn='6' then qty end) as [?6],MAX(case when rn='7' then qty end) as [?7],
	MAX(case when rn='8' then qty end) as [?8],MAX(case when rn='9' then qty end) as [?9],
	MAX(case when rn='10' then qty end) as [?10] 
	from #T 
	group by  ordeno ,pnno,dsc,(rn1-1)/10

drop table #T

效果：

第11个换行了。。