Home » SQL & PL/SQL » SQL & PL/SQL » Need help for a query.
Need help for a query. [message #197427] Wed, 11 October 2006 04:26 Go to next message
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
Re: Need help for a query. [message #197442 is a reply to message #197427] Wed, 11 October 2006 05:00 Go to previous messageGo to next message
Littlefoot
Messages: 20897
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 Go to previous message
gkodakalla
Messages: 49
Registered: March 2005
Member
Thanks a lot for your quick help.

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


Current Time: Wed Dec 07 03:14:10 CST 2016

Total time taken to generate the page: 0.10805 seconds