A表
ID 名称
1 a
2 b
...B表
ID 类别 数目
1 aa 10
1 aa 20
1 aa 30
1 bb 15
1 bb 25
2 aa 10
2 aa 30
2 bb 10
2 bb 20
....得到如下结果
ID 名称 aa bb ..
1 a 60 40
2 b 40 30
..
ID 名称
1 a
2 b
...B表
ID 类别 数目
1 aa 10
1 aa 20
1 aa 30
1 bb 15
1 bb 25
2 aa 10
2 aa 30
2 bb 10
2 bb 20
....得到如下结果
ID 名称 aa bb ..
1 a 60 40
2 b 40 30
..
select a.id,a.名称,
sum(case b.类别 when 'aa' then b.数目 else 0 end) 'aa',
sum(case b.类别 when 'bb' then b.数目 else 0 end) 'bb'
from a,b
where a.id = b.id
group by a.id,a.名称
insert into A values(1, 'a')
insert into A values(2, 'b')
create table B(ID int,类别 varchar(10),数目 int)
insert into B values(1, 'aa', 20)
insert into B values(1, 'aa', 30)
insert into B values(1, 'bb', 15)
insert into B values(1, 'bb', 25)
insert into B values(2, 'aa', 10)
insert into B values(2, 'aa', 30)
insert into B values(2, 'bb', 10)
insert into B values(2, 'bb', 20)
go--静态SQL
select a.id,a.名称,
sum(case b.类别 when 'aa' then b.数目 else 0 end) 'aa',
sum(case b.类别 when 'bb' then b.数目 else 0 end) 'bb'
from a,b
where a.id = b.id
group by a.id,a.名称
/*
id 名称 aa bb
----------- ---------- ----------- -----------
1 a 50 40
2 b 40 30(所影响的行数为 2 行)
*/--动态SQL
declare @sql varchar(8000)
set @sql = 'select a.id,a.名称'
select @sql = @sql + ' , sum(case 类别 when ''' + 类别 + ''' then 数目 else 0 end) [' + 类别 + ']'
from (select distinct 类别 from b) as t
set @sql = @sql + ' from a,b where a.id = b.id group by a.id,a.名称'
exec(@sql)
/*
id 名称 aa bb
----------- ---------- ----------- -----------
1 a 50 40
2 b 40 30
*/drop table a,b
create table A(ID int,名称 varchar(10))
insert into A values(1, 'a')
insert into A values(2, 'b')
create table B(ID int,类别 varchar(10),数目 int)
insert into B values(1, 'aa', 10)
insert into B values(1, 'aa', 20)
insert into B values(1, 'aa', 30)
insert into B values(1, 'bb', 15)
insert into B values(1, 'bb', 25)
insert into B values(2, 'aa', 10)
insert into B values(2, 'aa', 30)
insert into B values(2, 'bb', 10)
insert into B values(2, 'bb', 20)
go--静态SQL,指类别只有aa,bb
select a.id,a.名称,
sum(case b.类别 when 'aa' then b.数目 else 0 end) 'aa',
sum(case b.类别 when 'bb' then b.数目 else 0 end) 'bb'
from a,b
where a.id = b.id
group by a.id,a.名称
/*
id 名称 aa bb
----------- ---------- ----------- -----------
1 a 60 40
2 b 40 30(所影响的行数为 2 行)
*/--动态SQL,指类别不止aa,bb
declare @sql varchar(8000)
set @sql = 'select a.id,a.名称'
select @sql = @sql + ' , sum(case 类别 when ''' + 类别 + ''' then 数目 else 0 end) [' + 类别 + ']'
from (select distinct 类别 from b) as t
set @sql = @sql + ' from a,b where a.id = b.id group by a.id,a.名称'
exec(@sql)
/*
id 名称 aa bb
----------- ---------- ----------- -----------
1 a 60 40
2 b 40 30
*/drop table a,b
动态SQL
用case when
insert into 表A select 1,'a'
insert into 表A select 2,'b'create table 表B (id int,类别 varchar(10),数目 int)
insert into 表B select 1,'aa',10
insert into 表B select 1,'aa',20
insert into 表B select 1,'aa',30
insert into 表B select 1,'bb',15
insert into 表B select 1,'bb',25
insert into 表B select 2,'aa',10
insert into 表B select 2,'aa',30
insert into 表B select 2,'bb',10
insert into 表B select 2,'bb',20declare @sql varchar(1000)
set @sql='select a.id,a.名称'
select @sql=@sql+',['+类别+']=sum(case 类别 when '''+类别+''' then 数目 else 0 end)' from (select distinct 类别 from 表B)n
print @sql
set @sql= @sql+' from 表A a,表B b where a.id=b.id group by a.id,名称'
exec(@sql)