SQL & PL/SQL

Hi all,

I'm trying to get for a given set of items all possible combinations.

For example for items (A,B,C) I want to output all the possible combinations ordered from left to right:

A
B
C
AB
AC
BC
ABC

basically I want the output of sys_connect_by_path but instead of having the groups concatenated in the same row, I want to have them in rows. The query bellow gives me the output I want, but has the limitation that sys_connect_by_path can't handle sizes bigger than 4000.

WITH t AS
 (SELECT 1 seqno,
         'A' txt
    FROM dual
  UNION ALL
  SELECT 2 seqno,
         'B' txt
    FROM dual
  UNION ALL
  SELECT 3 seqno,
         'C' txt
    FROM dual
  UNION ALL
  SELECT 4 seqno,
         'D' txt
    FROM dual),
src AS
 (SELECT rownum combination_id,
         substr(sys_connect_by_path(txt, ','), 2) txt
    FROM t
  CONNECT BY seqno > PRIOR seqno)
SELECT combination_id,
       regexp_substr(txt, '[^,]+', 1, l) list
  FROM (SELECT DISTINCT combination_id,
                        txt,
                        column_value l
          FROM src,
               TABLE(CAST(MULTISET (SELECT LEVEL
                             FROM dual
                           CONNECT BY LEVEL <= nvl(length(regexp_replace(src.txt, '[^,]+', NULL)), 0) + 1) AS
                          sys.odcivarchar2list)))
 ORDER BY combination_id,
          l;

Is it possible to get this output in SQL without using sys_connect_by_path?

Thanks in advance.

Edited by: Manuel Vidigal on 21/Mar/2011 10:45