这样的输出怎么实现？
有两张表
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,table2
/*
a 甲 乙 丙 丁
----------- ----------- ----------- ----------- -----------
1 10 20 30 40
（所影响的行数为 1 行）
a 丙 丁 甲 乙
----------- ----------- ----------- ----------- -----------
1 30 40 10 20
*/
[解决办法]
select A.c[甲],B.c[乙],C.c[丙],D.c[丁] from
(select c from table1 where b= '1 'and a= '1 ')A,
(select c from table1 where b= '2 'and a= '1 ')B,
(select c from table1 where b= '3 'and a= '1 ')C,
(select c from table1 where b= '4 'and a= '1 ')D
