I have this data and expected result:

    Data                Expected Result 
No  A   B                  A    B
1   10  500               10    500                          
2   10  c=20              20    400
3   20  400               30    600
4   30  600               30    700
5   30  c=40              30    800
6   30  c=50              40    700
7   40  700               50    900
8   50  c=60              60    900
9   60  c=70              70    900
10  70  900               10    400

I need to perform a self join and get the result.

• For line number 1 the expected result is same as the row.
• For line number 2, I need to take the substring of column B (c=20) as 20 and join with column B and get the result as 400.
• Lines number 5 and 6 need to substring column B and get the result from column A.

I tried a recursive query, but still am not getting the expected result.

with rec(A, B, nested) as
(
select A, B, case when instr(B, 'C=') != 0 then substr(B, instr(B, 'C=')) as nested
from table
union all
select A, rec.B from table
inner join rec
on (table.A = rec.nested)
)
select A, B, nested from rec;

CodePudding user response：

Answer for the initial version of the question

You do not need a recursive query. To get your desired output you just need to exclude the rows where B starts with c=:

SELECT a, b
FROM   table_name
WHERE b NOT LIKE 'c=%';

Which, for the sample data:

CREATE TABLE table_name (no, a, b) AS
  SELECT 1, 10, '500'  FROM DUAL UNION ALL
  SELECT 2, 10, 'c=20' FROM DUAL UNION ALL
  SELECT 3, 20, '400'  FROM DUAL UNION ALL
  SELECT 4, 30, '600'  FROM DUAL UNION ALL
  SELECT 5, 30, 'c=40' FROM DUAL UNION ALL
  SELECT 6, 30, 'c=50' FROM DUAL UNION ALL
  SELECT 7, 40, '700'  FROM DUAL UNION ALL
  SELECT 8, 50, '800'  FROM DUAL;

Outputs your desired output:

fiddle

Answer for the 3rd edit of the question

You can use a hierarchical query:

SELECT DISTINCT
       CONNECT_BY_ROOT a AS a,
       b
FROM   table_name
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY
       PRIOR b LIKE 'c=%'
AND    PRIOR SUBSTR(b, 3) = a
ORDER BY a, b;

Which, for the sample data:

CREATE TABLE table_name (no, a, b) AS
  SELECT  1, 10, '500'  FROM DUAL UNION ALL
  SELECT  2, 10, 'c=20' FROM DUAL UNION ALL
  SELECT  3, 20, '400'  FROM DUAL UNION ALL
  SELECT  4, 30, '600'  FROM DUAL UNION ALL
  SELECT  5, 30, 'c=40' FROM DUAL UNION ALL
  SELECT  6, 30, 'c=50' FROM DUAL UNION ALL
  SELECT  7, 40, '700'  FROM DUAL UNION ALL
  SELECT  8, 50, 'c=60' FROM DUAL UNION ALL
  SELECT  9, 60, 'c=70' FROM DUAL UNION ALL
  SELECT 10, 70, '900'  FROM DUAL;

Outputs:

fiddle