Getting results into 2 columns from one

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Getting results into 2 columns from one

Author

Topic

Teachme
Starting Member

45 Posts

Posted - 2006-11-03 : 10:52:17

Hi if i have 2 tables(tbl A, tbl B). Tbl A has 2 columns (account,cash) and it has only one record. Tbl B has 3 columns(account,deposits,transactionCode) as well. They are joined by account and it has 20 records. In my view i wana retrieve everything from tblA which is only one record and from tblb i wana retrieve everything as well but i wana split deposits column into two columns as deposits_one and deposits_two based on transactionCodes but i dont want the cash from tblA to be repeated because i have to sum them at the end. my data is something like this

Sample
tbl A
account cash
123 100

tblb
account deposits trnansactionCode
123 50 5
123 75 10
123 200 5
123 80 10
123 90 5

The result should look something like this
account cash deposits1 deposits2
123 100 340(sum of deposits where code is 5) 155(for code 10)

how do i go about getting something like this. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-03 : 11:02:48

[code]select a.accont, a.cash,
deposits1 = sum(case when trnansactionCode = 5 then deposits else 0 end),
deposits2 = sum(case when trnansactionCode = 10 then deposits else 0 end)
from tbla a inner join tblb b
on a.account = b.account
group by a.accont, a.cash
[/code]

Teachme
Starting Member

45 Posts

Posted - 2006-11-03 : 11:30:59

well this query doesnt give me the result im after. caz i have to do a sum on a.cash as well if i do tat it would give me 500 for cash which is not right. Is there any other way around this. because if just do a select with no sums it puts 100 for cash in each record repeated 5 times if somehow it just gives 100 once then i can sum it up and i wud get wat i want..thanks

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-03 : 12:20:35

Why do need to sum a.cash? It is part of the grouping?

Jay White

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-03 : 12:45:47

quote:
Originally posted by Teachme

well this query doesnt give me the result im after. caz i have to do a sum on a.cash as well if i do tat it would give me 500 for cash which is not right. Is there any other way around this. because if just do a select with no sums it puts 100 for cash in each record repeated 5 times if somehow it just gives 100 once then i can sum it up and i wud get wat i want..thanks

What do you want actually ?
Post more sample data and the expected result !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-03 : 13:02:00

select a.accont, a.cash, sum(b.deposits),
deposits1 = sum(case when trnansactionCode = 5 then deposits else 0 end),
deposits2 = sum(case when trnansactionCode = 10 then deposits else 0 end)
from tbla a inner join tblb b
on a.account = b.account
group by a.accont, a.cash

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources