Home » SQL & PL/SQL » SQL & PL/SQL » Easier Way
Easier Way [message #197527] Wed, 11 October 2006 11:35 Go to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
Is there an easier way to achieve the following:

select field1,
sum(decode(field2,1,1,0)) sum_1,
sum(decode(field2,2,1,0)) sum_2
from table1
group by field1;

Now I have say 100 different values for field2,
is there a way to structure this so I dont need 100 different decodes.

[Updated on: Wed, 11 October 2006 11:36]

Report message to a moderator

Re: Easier Way [message #197609 is a reply to message #197527] Thu, 12 October 2006 01:02 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Let SQL generate the SQL for you Wink
CREATE TABLE table1(field1 NUMBER)
/

BEGIN
  FOR i IN 1..1000 LOOP
    INSERT INTO table1 VALUES (FLOOR(DBMS_RANDOM.VALUE(0,31)));
  END LOOP;
END;
/
SET PAGES 100
SET FEEDB OFF
SET HEAD  OFF
SPOOL theselect.sql
SELECT DECODE(rownum,1,'SELECT ','     , ')
    ||' SUM(DECODE(field1,'||field1||',1,0))'
    ||' sum_'||field1
FROM   ( SELECT DISTINCT field1 FROM table1 )
UNION ALL
SELECT 'FROM table1'
FROM dual
UNION ALL
SELECT '/'||chr(10)
FROM dual
/

spool off
SET FEEDB ON
SET HEAD  ON
@theselect

DROP TABLE table1
/
When I ran the above script I got this:
SQL> @orafaq

Table created.


PL/SQL procedure successfully completed.


SELECT  SUM(DECODE(field1,0,1,0)) sum_0
     ,  SUM(DECODE(field1,1,1,0)) sum_1
     ,  SUM(DECODE(field1,2,1,0)) sum_2
     ,  SUM(DECODE(field1,3,1,0)) sum_3
     ,  SUM(DECODE(field1,4,1,0)) sum_4
     ,  SUM(DECODE(field1,5,1,0)) sum_5
     ,  SUM(DECODE(field1,6,1,0)) sum_6
     ,  SUM(DECODE(field1,7,1,0)) sum_7
     ,  SUM(DECODE(field1,8,1,0)) sum_8
     ,  SUM(DECODE(field1,9,1,0)) sum_9
     ,  SUM(DECODE(field1,10,1,0)) sum_10
     ,  SUM(DECODE(field1,11,1,0)) sum_11
     ,  SUM(DECODE(field1,12,1,0)) sum_12
     ,  SUM(DECODE(field1,13,1,0)) sum_13
     ,  SUM(DECODE(field1,14,1,0)) sum_14
     ,  SUM(DECODE(field1,15,1,0)) sum_15
     ,  SUM(DECODE(field1,16,1,0)) sum_16
     ,  SUM(DECODE(field1,17,1,0)) sum_17
     ,  SUM(DECODE(field1,18,1,0)) sum_18
     ,  SUM(DECODE(field1,19,1,0)) sum_19
     ,  SUM(DECODE(field1,20,1,0)) sum_20
     ,  SUM(DECODE(field1,21,1,0)) sum_21
     ,  SUM(DECODE(field1,22,1,0)) sum_22
     ,  SUM(DECODE(field1,23,1,0)) sum_23
     ,  SUM(DECODE(field1,24,1,0)) sum_24
     ,  SUM(DECODE(field1,25,1,0)) sum_25
     ,  SUM(DECODE(field1,26,1,0)) sum_26
     ,  SUM(DECODE(field1,27,1,0)) sum_27
     ,  SUM(DECODE(field1,28,1,0)) sum_28
     ,  SUM(DECODE(field1,29,1,0)) sum_29
     ,  SUM(DECODE(field1,30,1,0)) sum_30
FROM table1
/

     SUM_0      SUM_1      SUM_2      SUM_3      SUM_4      SUM_5      SUM_6
---------- ---------- ---------- ---------- ---------- ---------- ----------
     SUM_7      SUM_8      SUM_9     SUM_10     SUM_11     SUM_12     SUM_13
---------- ---------- ---------- ---------- ---------- ---------- ----------
    SUM_14     SUM_15     SUM_16     SUM_17     SUM_18     SUM_19     SUM_20
---------- ---------- ---------- ---------- ---------- ---------- ----------
    SUM_21     SUM_22     SUM_23     SUM_24     SUM_25     SUM_26     SUM_27
---------- ---------- ---------- ---------- ---------- ---------- ----------
    SUM_28     SUM_29     SUM_30
---------- ---------- ----------
        19         40         35         41         24         32         36
        33         35         30         43         28         27         37
        34         34         32         35         27         36         25
        38         29         31         34         30         21         32
        37         29         36


1 row selected.


Table dropped.

SQL>


Of course, the create statement and insert statements are included to generate some sample data.

MHE

[Updated on: Thu, 12 October 2006 01:03]

Report message to a moderator

Previous Topic: PLEASE HELP ME! ERROR STRIKES ME!!
Next Topic: How to read a text file ?
Goto Forum:
  


Current Time: Sat Dec 10 14:57:39 CST 2016

Total time taken to generate the page: 0.17011 seconds