select identity(int,1,1) as id, loss_rt,qty into #t from lsbom group by loss_rt,qtyselect sum(case when id=1 then loss_rt else 0) as loss_rt1 sum(case when id=1 then qty else 0) as qty1 sum(case when id=2 then loss_rt else 0) as loss_rt2 sum(case when id=2 then qty else 0) as qty2 sum(case when id=3 then loss_rt else 0) as loss_rt3 sum(case when id=3 then qty else 0) as qty3 sum(case when id=4 then loss_rt else 0) as loss_rt4 sum(case when id=4 then qty else 0) as qty4 from #tdrop table #t
declare @sql varchar(8000) set @sql = '' select @sql = @sql + ',sum(case no when '+cast(no as varchar)+' then loss_rt end) ['+'loss_rt'+cast(no as varchar)+']' +',sum(case no when '+cast(no as varchar)+' then qty end) ['+'qty'+cast(no as varchar)+']' from (select distinct no from lsbom) as a select @sql = 'select '+right(@sql,len(@sql)-1)+' from lsbom' select @sql exec(@sql)
测试数据:create table lsbom(no int,loss_rt decimal(18,4),qty decimal(18,4)) insert lsbom select 1, 23, .2323 union select 2 , 2.33, 2.232 union select 3 , 2.2 , .003 union select 4 , 0.04 , 3.4 union select 5 ,0.11,2.33select * from lsbomdeclare @sql varchar(8000) set @sql = '' select @sql = @sql + ',sum(case no when '+cast(no as varchar)+' then loss_rt end) ['+'loss_rt'+cast(no as varchar)+']' +',sum(case no when '+cast(no as varchar)+' then qty end) ['+'qty'+cast(no as varchar)+']' from (select distinct no from lsbom) as a select @sql = 'select '+right(@sql,len(@sql)-1)+' from lsbom' select @sql exec(@sql) drop table lsbom结果: ------------loss_rt1 qty1 loss_rt2 qty2 loss_rt3 qty3 loss_rt4 qty4 loss_rt5 qty5 --------------------------------------- ---------------------------------------- 23.0000 .2323 2.3300 2.2320 2.2000 .0030 .0400 3.4000 .1100 2.3300
exec('select lss_decp'+@s+',' + @n +' from('+@tb+') a group by lss_decp')
from(select distinct a.loss_rt,a.qty from lsbom a)a的结果为下:
loss_rt qty
1 23 .2323
2 2.33 2.232
3 2.2 .003
4 0.04 3.4
......我现在想用语句select @s=@s+',['+'loss_rt]'+cast(@n as varchar)+',['+'qty]'+cast(@n as varchar),@n=@n+1
from(select distinct a.loss_rt,a.qty from lsbom a)a达到以下效果:
loss_rt1 qty1 loss_rt2 qty2 loss_rt3 qty3 loss_rt4 qty4..........
23 .2323 2.33 2.232 2.2 .003 0.04 3.4
请问错在哪?
into #t
from lsbom
group by loss_rt,qtyselect
sum(case when id=1 then loss_rt else 0) as loss_rt1
sum(case when id=1 then qty else 0) as qty1
sum(case when id=2 then loss_rt else 0) as loss_rt2
sum(case when id=2 then qty else 0) as qty2
sum(case when id=3 then loss_rt else 0) as loss_rt3
sum(case when id=3 then qty else 0) as qty3
sum(case when id=4 then loss_rt else 0) as loss_rt4
sum(case when id=4 then qty else 0) as qty4
from #tdrop table #t
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',sum(case no when '+cast(no as varchar)+' then loss_rt end) ['+'loss_rt'+cast(no as varchar)+']'
+',sum(case no when '+cast(no as varchar)+' then qty end) ['+'qty'+cast(no as varchar)+']'
from (select distinct no from lsbom) as a
select @sql = 'select '+right(@sql,len(@sql)-1)+' from lsbom'
select @sql
exec(@sql)
insert lsbom select 1, 23, .2323
union select 2 , 2.33, 2.232
union select 3 , 2.2 , .003
union select 4 , 0.04 , 3.4
union select 5 ,0.11,2.33select * from lsbomdeclare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',sum(case no when '+cast(no as varchar)+' then loss_rt end) ['+'loss_rt'+cast(no as varchar)+']'
+',sum(case no when '+cast(no as varchar)+' then qty end) ['+'qty'+cast(no as varchar)+']'
from (select distinct no from lsbom) as a
select @sql = 'select '+right(@sql,len(@sql)-1)+' from lsbom'
select @sql
exec(@sql)
drop table lsbom结果:
------------loss_rt1 qty1 loss_rt2 qty2 loss_rt3 qty3 loss_rt4 qty4 loss_rt5 qty5
--------------------------------------- ----------------------------------------
23.0000 .2323 2.3300 2.2320 2.2000 .0030 .0400 3.4000 .1100 2.3300