Home » SQL & PL/SQL » SQL & PL/SQL » please advice me how to apply decode for two or more column (pivot table)
please advice me how to apply decode for two or more column (pivot table) [message #221213] Sun, 25 February 2007 09:49 Go to next message
Smith_X
Messages: 56
Registered: January 2007
Member
hello, I have question about how to use decode which I do not understand it correctly.

Example I have data like below,
Code		Date		Amount
10007		4/2/2007	405
10004		29/1/2007	423
10007		16/1/2007	475
10002		20/2/2007	500
10009		26/1/2007	498
10006		22/12/2006	574
10002		17/2/2007	590
10010		23/11/2006	530
10003		22/12/2006	481
10003		25/1/2007	461
10000		21/2/2007	456
10006		3/12/2006	443
10002		9/1/2007	427
10002		15/2/2007	535
10004		22/11/2006	410
10009		11/12/2006	426
10010		28/1/2007	506
10005		19/2/2007	570
10003		7/12/2006	473
10005		1/1/2007	519
10006		27/1/2007	479
10002		4/12/2006	588
10009		2/12/2006	511
10002		24/12/2006	579


I would like to create an pivot table like below

Code	0-30 days	31-60 days	61-90 days	 > 90 days
10001		xx		xx		xx		xx
10002		xx		xx		xx		xx
10003		xx		xx		xx		xx
10004		xx		xx		xx		xx
10005		xx		xx		xx		xx
10006		xx		xx		xx		xx
10007		xx		xx		xx		xx
10008		xx		xx		xx		xx
10009		xx		xx		xx		xx
10010		xx		xx		xx		xx
10011		xx		xx		xx		xx
10012		xx		xx		xx		xx
10013		xx		xx		xx		xx
10014		xx		xx		xx		xx	
10015		xx		xx		xx		xx
10016		xx		xx		xx		xx
10017		xx		xx		xx		xx

Then, I try to use decode like below..

SELECT CODE
, SUM (DECODE (DATE >= SYSDATE - 30 and DATE <= SYSDATE, (AMOUNT), 0) '0-30 days'
, SUM (DECODE (DATE >= SYSDATE - 60 and DATE < SYSDATE - 30, (AMOUNT), 0) '31-60 days'
, SUM (DECODE (DATE >= SYSDATE - 90 and DATE < SYSDATE - 60, (AMOUNT), 0) '61-90 days'
, SUM (DECODE (DATE > SYSDATE - 90, (AMOUNT), 0) 'more than 90 days'

FROM SUPPLIER_TRANSACTION
GROUP BY CODE
ORDER BY CODE;


it's obviously I try to use two column in the same decode sentence which is not correct. well, I try to imaging how to create the code but I'm blank T_T

could anybody please point me out where I get it all wrong.. please~~
Re: please advice me how to apply decode for two or more column (pivot table) [message #221215 is a reply to message #221213] Sun, 25 February 2007 11:23 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select code
  2     , sum( case when t_date >= sysdate - 30 then amount else 0 end) "0-30 days"
  3     , sum( case when t_date >= sysdate - 60 and t_date < sysdate - 30 then amount else 0 end) "31-60 days"
  4     , sum( case when t_date >= sysdate - 90 and t_date < sysdate - 60 then amount else 0 end) "61-90 days"
  5     , sum( case when t_date < sysdate - 90 then amount else 0 end) "more than 90 days"
  6  from supplier_transaction
  7  group by code
  8  order by code;

      CODE  0-30 days 31-60 days 61-90 days more than 90 days
---------- ---------- ---------- ---------- -----------------
     10000        456          0          0                 0
     10002       1625        427       1167                 0
     10003          0        461        954                 0
     10004        423          0          0               410
     10005        570        519          0                 0
     10006        479          0       1017                 0
     10007        405        475          0                 0
     10009          0        498        937                 0
     10010        506          0          0               530

9 rows selected.
Re: please advice me how to apply decode for two or more column (pivot table) [message #221237 is a reply to message #221215] Sun, 25 February 2007 22:48 Go to previous message
Smith_X
Messages: 56
Registered: January 2007
Member
Oh Thank you very much, ebrian. CASE statement seems easier than DECODE. Razz

(Actually, I still confuse how to use decode properly. I will try to study more.)
Previous Topic: Split the name into seperate columns
Next Topic: join
Goto Forum:
  


Current Time: Sun Dec 04 10:59:00 CST 2016

Total time taken to generate the page: 0.09520 seconds