这样的输出怎么实现？
有两张表

table1
a       b       c
1       1       10
1       2       20  
1       3       30    
1       4       40

table2
b       b_name
1         甲
2         乙
3         丙
4         丁

现有要求是，给定a=1，输出如下：
甲     乙     丙     丁
10     20     30     40

这样的输出怎么实现？


------解决方案--------------------
insert into T
select 1, 1, 10
union all
select 1, 2, 20
union all
select 1, 3, 30
union all
select 1, 4, 40

create table b(b int,b_name varchar(10))
insert into b
select 1, '甲 '
union all
select 2, '乙 '
union all
select 3, '丙 '
union all
select 4, '丁 '

select * from T
select * from b

select b_name,c from T inner join b on T.b=b.b and T.a=1

alter proc dbo.proc_change(@id int)
as
begin
declare @sql varchar(8000)
select T.a,b_name,c into #temp from T inner join b on T.b=b.b and T.a=@id
set @sql = 'select a, '
select @sql = @sql + ' sum(case b_name when ' ' '+b_name+ ' ' ' then c end) as [ '+b_name+ '], '
from #temp
select @sql=left(@sql,len(@sql)-1) + ' from #temp group by a '
-- print @sql
exec(@sql)
end


proc_change 1
--delete test data
drop proc dbo.proc_change
drop table T
drop table b
------解决方案--------------------
if object_id( 'pubs..table1 ') is not null
drop table table1
go
create table table1(a int,b int,c int)
insert into table1(a,b,c) values(1, 1, 10)
insert into table1(a,b,c) values(1, 2, 20 )
insert into table1(a,b,c) values(1, 3, 30)
insert into table1(a,b,c) values(1, 4, 40)
go

if object_id( 'pubs..table2 ') is not null
drop table table2
go
create table table2(b int,b_name varchar(10))
insert into table2(b,b_name) values(1, '甲 ')
insert into table2(b,b_name) values(2, '乙 ')
insert into table2(b,b_name) values(3, '丙 ')
insert into table2(b,b_name) values(4, '丁 ')
go

--静态SQL
select a ,
max(case when b_name = '甲 ' then c else 0 end) as 甲,
max(case when b_name = '乙 ' then c else 0 end) as 乙,
max(case when b_name = '丙 ' then c else 0 end) as 丙,
max(case when b_name = '丁 ' then c else 0 end) as 丁
from
(
select a.a , a.c,b.b_name from table1 a , table2 b where a.b = b.b and a.a = 1
) t
group by a

--动态SQL
declare @sql varchar(8000)
set @sql = 'select a '
select @sql = @sql + ' , max(case b_name when ' ' ' + b_name + ' ' ' then c else 0 end) [ ' + b_name + '] '
from (select distinct b_name from (select a.a , a.c,b.b_name from table1 a , table2 b where a.b = b.b and a.a = 1) t) as m
set @sql = @sql + ' from (select a.a , a.c,b.b_name from table1 a , table2 b where a.b = b.b and a.a = 1) t group by a '
exec(@sql)

drop table table1,t