Handling circular data in Oracle's SQL

Question

Hi everyone,

There is a problem that don't know how to resolve only with SQL (I could do it with PL/SQL, but it should be doable only with SQL).

I have a table (in fact it's not a table but the result of a with query) with contains pair of values like this:

  column1    column2 
 ---------  ---------
   value1    value2
   value1    value3
   value2    value4
   value3    value7
   value8    value9

The ouput I want for that data would be (I don't care about the order):

  output_column
 ---------------
  value1, value2, value3, value4, value7
  value8, value9

In other words, I want to get disjoint sets of data connected by any of both values.

Every pair in the input table is unique (the minor value is always at the left, and I have used a distinct to compute the input table).

I have absolutely no clue about how to do this with model, and my efforts with connect by complain about "circular data". This at first doesn't look to hard to do, but can't figure how to do it in a non procedural way. Any thoughts?

Thanks in advance.

Answer 1

First of all I'd check your data. The example data you provided doesn't look circular and connect by shouldn't error.

If your data is as follows then this would error:

column1    column2 
 ---------  ---------
   value1    value2
   value1    value3
   value2    value4
   value3    value7
 **value7    value1**
   value8    value9

In Oracle 10g you can specify NOCYCLE for oracle to return rows even if a CONNECT BY LOOP exists.

Answer 2

Hi Samuel,

The following query will work with your data set:

SQL> SELECT root || ',' || stragg(DISTINCT column2)
  2    FROM (SELECT CONNECT_BY_ROOT(column1) root, t.*
  3             FROM t
  4           CONNECT BY PRIOR column2 = column1
  5            START WITH column1 IN (SELECT column1
  6                                     FROM t
  7                                    WHERE column1 NOT IN (SELECT column2
  8                                                            FROM t))) v
  9   GROUP BY root;

ROOT||','||STRAGG(DISTINCTCOLU
-----------------------------------------
value1,value2,value3,value4,value7
value8,value9

I'm using Tom Kyte's concatenation stragg function.