Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mangyun
Starting Member
8 Posts |
 Posted - 2007-12-14 : 03:00:57
|
| --i have this tabledeclare @a table(a1 varchar(3),a2 varchar(3),a3 int)insert into @aselect 'aa','11',1 union allselect 'aa','11',1 union allselect 'aa','12',1 union allselect 'aa','12',1 union allselect 'aa','12',1 union allselect 'aa','12',1 union allselect 'bb','11',1 union allselect 'bb','12',1 union allselect 'bb','12',1 --known solutionselect c1.a1,c1.v11,c2.v12 from (select a1,sum(a3) v11 from @a where a2='11' group by a1) as c1 left join(select a1,sum(a3) v12 from @a where a2='12' group by a1) as c2 on c1.a1=c2.a1is there any other way to acomplish the same result(as the above query) without using subquery ?regards |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-12-14 : 03:15:27
|
| [code]select a1, sum(case when a2='11' then a3 else 0 end) as v11, sum(case when a2='12' then a3 else 0 end) as v12from @agroup by a1[/code]MadhivananFailing to plan is Planning to fail |
 |
|
|
mangyun
Starting Member
8 Posts |
Posted - 2007-12-14 : 03:40:42
|
| :) thanks Madhivanan.actually i have used case when.... a few times before ....just got confused :( and unclear mind :)very much apreciatedmangyun |
|
|
|
|
|
|
|
|
non subquery .....