高难度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
/
📱 扫码关注公众号
扫描二维码关注我们，获取更多精彩内容
实时更新 · 干货满满