OraFAQ Forum: SQL & PL/SQL » Need help for a query.

Home » SQL & PL/SQL » SQL & PL/SQL » Need help for a query.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need help for a query. [message #197427]

Wed, 11 October 2006 04:26

gkodakalla
Messages: 49
Registered: March 2005

Member

CREATE TABLE KGR (CSP_NUM CHAR(9), PYBL_MTH NUMBER,CF_AMOUNT NUMBER);

INSERT INTO KGR VALUES ('459200101',1,100);
INSERT INTO KGR VALUES ('459200101',2,200);
INSERT INTO KGR VALUES ('459200101',9,900);
COMMIT;

PYBL_MTH has details of month. assume i work only for this year. so i might be having only 12 records for a CSP_NUM maximum.
CF_AMOUNT is Cash Flow amount for that month for that csp_num.

my requirement is like this. I need to get data in this format.
we need to prepare a string for 12 months of a year.
If there is value for that month, we would put that value in that position.If there is no value, put "-" in that position.

CSP_NUM PYBL_VALUE
459200101 100|200|-|-|-|-|-|-|900|-|-|-|

Can you please help me in framing this query.

Giridhar Kodakalla

Report message to a moderator

Re: Need help for a query. [message #197442 is a reply to message #197427]

Wed, 11 October 2006 05:00

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Something like this?

SELECT csp_num ||'|'||
           MAX(DECODE(pybl_mth,  1, TO_CHAR(cf_amount), '-')) ||'|'||
	   MAX(DECODE(pybl_mth,  2, TO_CHAR(cf_amount), '-')) ||'|'||
	   MAX(DECODE(pybl_mth,  3, TO_CHAR(cf_amount), '-')) ||'|'||
	   MAX(DECODE(pybl_mth,  4, TO_CHAR(cf_amount), '-')) ||'|'||
	   MAX(DECODE(pybl_mth,  5, TO_CHAR(cf_amount), '-')) ||'|'||
	   MAX(DECODE(pybl_mth,  6, TO_CHAR(cf_amount), '-')) ||'|'||
	   MAX(DECODE(pybl_mth,  7, TO_CHAR(cf_amount), '-')) ||'|'||
	   MAX(DECODE(pybl_mth,  8, TO_CHAR(cf_amount), '-')) ||'|'||
	   MAX(DECODE(pybl_mth,  9, TO_CHAR(cf_amount), '-')) ||'|'||
	   MAX(DECODE(pybl_mth, 10, TO_CHAR(cf_amount), '-')) ||'|'||
	   MAX(DECODE(pybl_mth, 11, TO_CHAR(cf_amount), '-')) ||'|'||
	   MAX(DECODE(pybl_mth, 12, TO_CHAR(cf_amount), '-')) ||'|'
FROM kgr
GROUP BY csp_num;

[Updated on: Wed, 11 October 2006 05:00]

Report message to a moderator

Re: Need help for a query. [message #197458 is a reply to message #197427]

Wed, 11 October 2006 05:29

gkodakalla
Messages: 49
Registered: March 2005

Member

Thanks a lot for your quick help.

Giridhar

Report message to a moderator

Previous Topic:	display of sum 1 to 100
Next Topic:	about sQL error....

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 11:43:29 CDT 2024