declare @s varchar(8000)
select distinct px = identity(int,1,1),业务员 into # from tb order by 业务员select @s = Isnull(@s+',','') + '[业务员'+ltrim(px)+'销量]= sum(case when b.业务员 = '''+业务员+''' then 销售数量 else 0 end'
from # order by pxexec('select a.编号,a.名称,'+ @s+ ' from ta a,tb b where a.编号 = b.编号 group by a.编号,a.名称')drop table #
select distinct px = identity(int,1,1),业务员 into # from tb order by 业务员select @s = Isnull(@s+',','') + '[业务员'+ltrim(px)+'销量]= sum(case when b.业务员 = '''+业务员+''' then 销售数量 else 0 end'
from # order by pxexec('select a.编号,a.名称,'+ @s+ ' from ta a,tb b where a.编号 = b.编号 group by a.编号,a.名称')drop table #
create table A(编号 varchar(10) , 名称 varchar(10))
insert into A values('01' , 'xx1')
insert into A values('02' , 'xx2')
insert into A values('03' , 'xx3')
insert into A values('04' , 'xx4')
insert into A values('05' , 'xx5')
create table B(编号 varchar(10), 业务员 varchar(10), 销售数量 int)
insert into B values('01' , 'ywy1' , 2000 )
insert into B values('01' , 'ywy2' , 300 )
insert into B values('01' , 'ywy3' , 150 )
insert into B values('02' , 'ywy1' , 3000 )
insert into B values('03' , 'ywy2' , 200 )
insert into B values('03' , 'ywy3' , 100 )
insert into B values('04' , 'ywy3' , 20 )
go--静态SQL,指每个编号最多三个业务员
select A.* ,
sum(case right(B.业务员,1) when '1' then 销售数量 else 0 end) 业务员1销量,
sum(case right(B.业务员,1) when '2' then 销售数量 else 0 end) 业务员2销量,
sum(case right(B.业务员,1) when '3' then 销售数量 else 0 end) 业务员3销量
from A,B
where A.编号 = B.编号
group by A.编号 , A.名称
/*
编号 名称 业务员1销量 业务员2销量 业务员3销量
---------- ---------- ----------- ----------- -----------
01 xx1 2000 300 150
02 xx2 3000 0 0
03 xx3 0 200 100
04 xx4 0 0 20(所影响的行数为 4 行)
*/
--动态SQL,指每个编号业务员数量不定
declare @sql varchar(8000)
set @sql = 'select A.* '
select @sql = @sql + ' , sum(case right(B.业务员,1) when ''' + 业务员 + ''' then 销售数量 else 0 end) [业务员' + 业务员 + '销量]'
from (select distinct right(B.业务员,1) 业务员 from B) as T
set @sql = @sql + ' from A,B where A.编号 = B.编号 group by A.编号 , A.名称'
exec(@sql)
/*
编号 名称 业务员1销量 业务员2销量 业务员3销量
---------- ---------- ----------- ----------- -----------
01 xx1 2000 300 150
02 xx2 3000 0 0
03 xx3 0 200 100
04 xx4 0 0 20
*/
drop table A,B