求一SQL语句

ykwang 2011-10-20 08:47:50
语句如下:
select dh,Savedata as a1,'' as a2 from savetable where savename='A'
union all
select dh,'' as A1,Savedata as a2 from savetable where savename='B'

假设得到的返回结果为:

DH a1 a2
001 A-500 空格 注:第一条语句返回 每个DH只会有一条记录
001 空格 B-300 注:第二条语句返回 每个DH只会有一条记录

我想实现的结果为

DH A1 A2
001 A-500 B-300

如何使单号相同,数据自动摆在一起。
...全文
36 点赞 收藏 5
写回复
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
rmini 2011-10-20
需求真是多样化啊,收藏了
回复
中国风 2011-10-20
直接用case when +Max

楼主语句改为
select dh,max(a1) as a1,max(a2) as a2
from (select dh,Savedata as a1,'' as a2 from savetable where savename='A'
union all
select dh,'' as A1,Savedata as a2 from savetable where savename='B')t
group by dh
回复
中国风 2011-10-20
select dh,max(case when savename = 'A'  then Savedata end) as a1,
max(case when savename = 'B' then Savedata end) as a2
from savetable
where savename in('A','B')
group by dh
回复
ykwang 2011-10-20
问题是 这个不是数值呢。 不能使用 sum
回复
唐诗三百首 2011-10-20

select dh,sum(a1) 'A1',sum(a2) 'A2'
from
(select dh,Savedata as a1,0 as a2 from savetable where savename='A'
union all
select dh,0 as A1,Savedata as a2 from savetable where savename='B'
) t
group by dh

回复
发动态
发帖子
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
社区公告
暂无公告