Handling circular data in Oracle's SQL

model, oracle, sql

Solution

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.

Problem

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.

Original source