if OBJECT_ID('tb') is not null
drop table [tb]
go
create table [tb](Item varchar(4),单重 int,Quantity int,Riqi varchar(5))
insert into [tb]
select 'A',2, 10, '8-10' union all
select 'A',2, 20, '8-12' union all
select 'B',3, 15, '8-12'
declare @s1 varchar(8000),
@s2 varchar(8000)
set @s1=''
set @s2=''
select @s1=@s1+','+QUOTENAME(Riqi)+' = sum( case Riqi when '''+Riqi+''' then Quantity else 0 end )'
from tb
group by Riqi
select @s2=@s2+','+QUOTENAME(Riqi)+' = sum( case Riqi when '''+Riqi+''' then 单重*Quantity else 0 end )'
from tb
group by Riqi
exec(' select Item ,单重' +@s1+' from tb group by Item ,单重
union all '+
'select ''数量汇总'',0' +@s1+' from tb
union all '+
'select ''重量汇总'',0'+@s2+' from tb')
drop table [tb]
go
create table [tb](Item varchar(4),单重 int,Quantity int,Riqi varchar(5))
insert into [tb]
select 'A',2, 10, '8-10' union all
select 'A',2, 20, '8-12' union all
select 'B',3, 15, '8-12'
declare @s1 varchar(8000),
@s2 varchar(8000)
set @s1=''
set @s2=''
select @s1=@s1+','+QUOTENAME(Riqi)+' = sum( case Riqi when '''+Riqi+''' then Quantity else 0 end )'
from tb
group by Riqi
select @s2=@s2+','+QUOTENAME(Riqi)+' = sum( case Riqi when '''+Riqi+''' then 单重*Quantity else 0 end )'
from tb
group by Riqi
exec(' select Item ,单重' +@s1+' from tb group by Item ,单重
union all '+
'select ''数量汇总'',0' +@s1+' from tb
union all '+
'select ''重量汇总'',0'+@s2+' from tb')
@s2 varchar(8000),
@s3 ..
@s4...
--定义多点变量
set @s1=''
set @s2=''
select @s1=@s1+','+QUOTENAME(Riqi)+' = sum( case Riqi when '''+Riqi+''' then Quantity else 0 end )'
from (select top xxxxx Riqi from tb) k
group by Riqiselect @s3=@s3+','+QUOTENAME(Riqi)+' = sum( case Riqi when '''+Riqi+''' then Quantity else 0 end )'
from (select top xxxxx Riqi from tb) k
group by Riqi
select @s2=@s2+','+QUOTENAME(Riqi)+' = sum( case Riqi when '''+Riqi+''' then 单重*Quantity else 0 end )'
from (select top xxxxx Riqi from tb) k
group by Riqi
exec(' select Item ,单重' +@s1+' from tb group by Item ,单重
union all '+
'select ''数量汇总'',0' +@s1+' from tb
union all '+
'select ''重量汇总'',0'+@s2+' from tb')
我大概描述了下 不知道你懂没多几个变量 把tb里面的Riqi 分几段写入变量
@table1 = 'select Item ,单重' +@s1+' from tb group by Item ,单重'
@table2 = 'select ''数量汇总'',0' +@s1+' from tb'
@table3 = 'select ''重量汇总'',0'+@s2+' from tb'
exec (@table1 + ' UNION ALL ' + @table2 + ' UNION ALL ' + @table3 )