Loop processing in Oracle SQL

Question

There is a problem that does not know how to resolve only with SQL (I could do it with PL / SQL, but this should only be done with SQL).

I have a table (in fact, this is not a table, but the result of query c) contains a couple of values:

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

I want this data to 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 datasets associated with either of the two values.

Each pair of the input table is unique (the secondary value is always on the left, and I used a separate one to calculate the input table).

I have absolutely no idea how to do this with the model, and my efforts with the connection, complaining about the "circular data". At first, this does not look difficult, but it cannot understand how to do it in a non-procedural way. Any thoughts?

Thanks in advance.

Answer 1

The following query will work with your dataset:

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 use Tom Kyte concatenation creation function .

Answer 2

First of all, I will check your details. The given example data does not look circular and are connected, and not with an error.

If your data looks like this, it will result in an error:

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

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