single row col value should reflect in multiple rows [message #317762] |
Sat, 03 May 2008 04:16  |
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 #317827 is a reply to message #317762] |
Sat, 03 May 2008 22:57   |
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 #318031 is a reply to message #317762] |
Mon, 05 May 2008 06:47  |
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
|
|
|