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
/

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>