Home » SQL & PL/SQL » SQL & PL/SQL » single row col value should reflect in multiple rows
single row col value should reflect in multiple rows [message #317762] Sat, 03 May 2008 04:16 Go to next message
rayessir
Messages: 5
Registered: April 2008
Junior Member
hi

i have one table

table name : collection

tdate date
desc1 varchar2(10) (values can be cash / cheque / card )
amt number(10,2)

datas are

01-jan-08 cash 1000.00
01-jan-08 card 500.00
02-jan-08 card 1200.00
03-jan-08 cash 450.00

I want to display the data as follows

date cash cheque card
01-jan08 1000.00 0.00 500.00
02-jan-08 0.00 0.00 1200.00
03-jan-08 450.00 0.00 0.00
total 1450.00 0.00 1700.00

is it possible through single select statement



Re: single row col value should reflect in multiple rows [message #317766 is a reply to message #317762] Sat, 03 May 2008 04:29 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Search for "pivot"; if you choose to use OraFAQ Forum's Search facility, I'd suggest you to filter by user "Maaher". If I remember well (and I believe I do), he posted an excellent example some time ago.
Re: single row col value should reflect in multiple rows [message #317771 is a reply to message #317762] Sat, 03 May 2008 05:45 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
try to use decode
yours
dr.s.raghunathan
Re: single row col value should reflect in multiple rows [message #317827 is a reply to message #317762] Sat, 03 May 2008 22:57 Go to previous messageGo to next message
spsonkusare
Messages: 3
Registered: May 2008
Junior Member
Hi,
Try decode function as follows

SELECT TDATE,DESC1,
DECODE(TRIM(DESC1),'CASH',AMT,0) AS 'CASH',
DECODE(TRIM(DESC1),'CHEQUE',AMT,0) AS 'CHEQUE',
DECODE(TRIM(DESC1),'CARD',AMT,0) AS 'CARD'
FROM COLLECTION

(Because you have DESC1 column as varchar2, trim is necessary)
Re: single row col value should reflect in multiple rows [message #317830 is a reply to message #317827] Sun, 04 May 2008 01:32 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
spsonkusare wrote on Sun, 04 May 2008 05:57

(Because you have DESC1 column as varchar2, trim is necessary)

Care to explain that? Why do you expect leading or trailing spaces in the column-values?

Further, your query does not aggregate rows into a single row.
Re: single row col value should reflect in multiple rows [message #318031 is a reply to message #317762] Mon, 05 May 2008 06:47 Go to previous message
ajay.yaleti
Messages: 1
Registered: October 2007
Junior Member
Hi,
Hope this will get you exact result for your solution.

SELECT DECODE(TDATE,NULL,'TOTAL',TDATE) TDATE , 
SUM(DECODE(DESC1,'cash',AMT,0)) CASH, 
SUM(DECODE(DESC1,'card',AMT,0)) CARD ,
SUM(DECODE(DESC1,'cheque',AMT,0)) CHEQUE 
FROM COLLECTION
GROUP BY rollup(TDATE)


Regards,
Ajay

[Updated on: Mon, 05 May 2008 06:49] by Moderator

Report message to a moderator

Previous Topic: faq's on Oracle SQL n PL/SQL
Next Topic: Update another table
Goto Forum:
  


Current Time: Thu Feb 13 09:42:14 CST 2025