紧急求助一个sql语句！！！！大家帮忙！！！

[meilidami]

有个表
tmp
nbr charge name
1 2 x
1 3 y
1 3 z
2 3 x
2 5 y
其中name 的类型只有 x,y,z 三种类型
现在要查询出下列格式：
nbr x y z
1 2 3 3
1 3 5 0
其中x,y,z 列下的数字为相应的nbr 对应的charge
大家帮忙啊！！！

[梅青松]

select distinct a.nbr,
(select charge from tmp where nbr = a.nbr and name = x) as x,
(select charge from tmp where nbr = a.nbr and name = y) as y,
(select charge from tmp where nbr = a.nbr and name = z) as z
from tmp as a

[jinjazz]

--测试case when的用法

--Ms SQL

CREATE TABLE [t1] (
[dz1] [int] NULL ,
[dz2] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO


insert into t1
select 1 ,'a'
union
select 1 ,'b'
union
select 1 ,'c'
union
select 2 ,'a'
union
select 2 ,'b'
union
select 2 ,'c'
union
select 3 ,'a'
union
select 3 ,'b'
union
select 3 ,'c'

select sum(countx) sumall,
min(case when dz2='a' then countx end) countx1,
min(case when dz2='b' then countx end) countx2,
min(case when dz2='c' then countx end) countx3
from
(select dz2,count(dz1) as countx from t1 group by dz2) a

[shitianj]

楼上正解~！
给人家分吧~

还有你如果用的是MYSQL的话，这句SQL可能不能执行

[shitianj]

我说的是一楼的~~

[yinweixian]

select distinct a.nbr,
(select charge from tmp where nbr = a.nbr and name = x) as x,
(select charge from tmp where nbr = a.nbr and name = y) as y,
(select charge from tmp where nbr = a.nbr and name = z) as z
from tmp as a

[Jumpi]

^_^，和我的问题一样！
http://community.csdn.net/Expert/TopicView.asp?id=3324969

[梅青松]

忘了加 ‘’了
select distinct a.nbr,
(select charge from tmp where nbr = a.nbr and name = 'x') as x,
(select charge from tmp where nbr = a.nbr and name = 'y') as y,
(select charge from tmp where nbr = a.nbr and name = 'z') as z
from tmp as a

[jinjazz]

--测试

--Ms SQL

CREATE TABLE [temp] (
[nbr] [int] NULL ,
[charge] [int] NULL ,
[name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO


insert into temp
select 1,2,'x'
union
select 1,3,'y'
union
select 1,3,'z'
union
select 2,3,'x'
union
select 2,5,'y'

select nbr,
min(case when name='x' then charge end) x,
min(case when name='y' then charge end) y,
min(case when name='z' then charge end) z
from temp group by nbr

drop TABLE temp

[drift1981]

meiqingsong(阿飛) jinjazz(人雅的标记--落寞刺客)的都可以，可以结了