高分SQL文求助——如何根据条件设定检索项目？

[sanlinkcn]

请教：
执行下列sql语句：
select name , money , money_type from table1
where money_type in ('01' , '02' , '05');
结果如下
name money money_type
--------------------------
张一 100 01
张一 200 02
张一 300 05
王二 400 01
王二 500 05

我希望检索出来的结果是
name money1 money2 money3
--------------------------------
张一 100 200 300
王二 400 0 500
其中money1,money2,money3分别是money_type为01,02,05时的金额。
请问要得到这样的结果，应该如何写sql语句？

[lilu207]

http://blog.csdn.net/xluzhong/articles/349046.aspx

[631799]

declare @s varchar(8000)
set @s=''
select @s=@s+',[money'+cast(money_type as int)+']=max(case money_type when '''+money_type+''' then money end)'
from 表 group by money_type order by money_type desc
exec('select name'+@s+'
from 表
group by name')
go

[子陌红尘]

--生成测试数据
create table #t(name varchar(10),money int,money_type varchar(10))
insert into #t select '张一',100,'01'
insert into #t select '张一',200,'02'
insert into #t select '张一',300,'05'
insert into #t select '王二',400,'01'
insert into #t select '王二',500,'05'


--执行查询
declare @s varchar(8000)
declare @i int
set @s = ''
set @i = 1
select
@s = @s+'money'+rtrim(@i)+'= sum(case money_type when '''+rtrim(money_type)+''' then money else 0 end)',
@i = @i + 1
from #t group by money_type order by money_type
exec('select name'+@s+' from #t group by name')


--输出结果
name money1 money2 money3
------ ------ ------ ------
王二 400 0 500
张一 100 200 300

[子陌红尘]

上面遗漏了一个逗号，修正如下：
---------------------------------------------------------------------------------
--生成测试数据
create table #t(name varchar(10),money int,money_type varchar(10))
insert into #t select '张一',100,'01'
insert into #t select '张一',200,'02'
insert into #t select '张一',300,'05'
insert into #t select '王二',400,'01'
insert into #t select '王二',500,'05'


--执行查询
declare @s varchar(8000)
declare @i int
set @s = ''
set @i = 1
select
@s = @s+',money'+rtrim(@i)+'= sum(case money_type when '''+rtrim(money_type)+''' then money else 0 end)',
@i = @i + 1
from #t group by money_type order by money_type
exec('select name'+@s+' from #t group by name')


--输出结果
name money1 money2 money3
------ ------ ------ ------
王二 400 0 500
张一 100 200 300

[sanlinkcn]

谢谢大家。我希望能用一个sql文来实现而不是PL-sql。
现在已经知道答案了。

select name,sum(decode(money_type,'01',money)) money1,
sum(decode(money_type,'02',money)) money2,
sum(decode(money_type,'05',money)) money3
from tbname
where money_type in('01','02','05') group by name;