|
View previous topic :: View next topic
|
| Author |
Message |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
My input table is,
| Code: |
Acct no dept amt
-------- ------ -------
0001 D001 10.00
0002 D001 11.00
0001 D001 08.00
0003 D003 12.12
0003 D004 10.10
0004 D002 12.10
|
out put should be,
| Code: |
Acct no dept1 dept2 dept3 dept4
-------- ------- ------- ------- -------
0001 18.00 0 0 0
0002 11.00 0 0 0
0003 0 0 12.12 10.10
0004 0 12.10 0 0
|
Could you please suggest the way getting the output done.
I tried with the CASE fuction but somehow couldn't success. |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
for a fixed number of departments :
| Code: |
select acct_no
,sum(case when dept = 'D001' then amt else 0) as dept1
,sum(case when dept = 'D002' then amt else 0) as dept2
,sum(case when dept = 'D003' then amt else 0) as dept3
,sum(case when dept = 'D004' then amt else 0) as dept4
from table1
group by acct_no |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
Thank you GuyC.
Just a little modification to the output, so i think we need to make a use of recursive query here..Please suggest
| Code: |
Acct no dept1 dept2 dept3 dept4
-------- ------- ------- ------- -------
0001 10.00 0 0 0
0001 08.00 0 0 0
0002 11.00 0 0 0
0003 0 0 12.12 10.10
0004 0 12.10 0 0
|
|
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
Your posts were edited to use the code tags, the make the data more readable by using a fixed font.
I FIXED the poor alignment..
the best way is to edit the text to be posted with a FIXED font editor and then cut and paste.
and ... USE the PREVIEW button to see how the post will look like |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| @enrico-sorichetti: Sure, I will keep this in my mind. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
the requirement as modified does not really make any sense ( is poorly described / is incomplete)
1) if an account has amounts for just one department ... OK as many lines as the different amounts
2) if an account has ONE amount for each department ... OK consolidate everything on one line
3) BUT if an account has N amounts for M departments what is the logic
| Code: |
Acct no dept amt
-------- ------ -------
0001 D001 10.00
0002 D001 11.00
0001 D001 08.00
0001 D002 110.00
0002 D002 111.00
0001 D002 208.00
0003 D003 12.12
0003 D004 10.10
0004 D002 12.10
|
how do You want the output ??? |
|
| Back to top |
|
|
Ed Goodman
Active Member
Joined: 08 Jun 2011
Posts: 556
Location: USA
|
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
Good one Ed! You beat me to it!  |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
You might find this one useful*.
You won't :-) |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
although it doesn't make sense, if you don't want the sum ; don't use sum() or group by :
| Code: |
select acct_no
,case when dept = 'D001' then amt else 0 end as dept1
,case when dept = 'D002' then amt else 0 end as dept2
,case when dept = 'D003' then amt else 0 end as dept3
,case when dept = 'D004' then amt else 0 end as dept4
from table1
order by acct_no |
ps. I forgot to code the 'end' in my previous post |
|
| Back to top |
|
|
|
|
