Recursive Iteration in Oracle

Question

I have a table like that:

 +----+-----+------+
 | id | ord | test |
 +----+-----+------+
 |  1 |   1 | A    |
 |  1 |   2 | B    |
 |  1 |   3 | C    |
 |  2 |   1 | B    |
 |  2 |   2 | C    |
 +----+-----+------+

(Here is some code for creating the data)

drop table temp_test;
create table temp_test (id varchar(20), ord varchar(20), test varchar(20));
insert into temp_test (id,ord,test) values ('1','1','A');
insert into temp_test (id,ord,test) values ('1','2','B');
insert into temp_test (id,ord,test) values ('1','3','C');
insert into temp_test (id,ord,test) values ('2','1','B');
insert into temp_test (id,ord,test) values ('2','2','C');
commit;

How could I get the following result?

+----+-----+-------+
| id | ord | test  |
+----+-----+-------+
|  1 |   1 | A     |
|  1 |   2 | A_B   |
|  1 |   3 | A_B_C |
|  2 |   1 | B     |
|  2 |   2 | B_C   |
+----+-----+-------+

I have tried using LAG(), something like:

  select CONCAT(lag(TEST) over (partition by ID order by ord),TEST) AS TEST from temp_test;

but it does not work recursively.

This code works:

SELECT 
R1.*,
(   SELECT  LISTAGG(test, ';') WITHIN GROUP (ORDER BY ord)
    FROM    temp_test R2
    WHERE   R1.ord >= R2.ord
    AND     R1.ID = R2.ID
    GROUP BY ID
) AS WTR_KEYWORD_1
FROM temp_test R1
ORDER BY id, ord;

but it is not performant enough for a larger data set.

Kaushik Nayak · Accepted Answer · 2018-11-19 17:31:39Z

1

Some say the Hierarchical queries are outdated, but they generally perform far better than recursive CTE

SELECT id,
       ord,
       LTRIM(sys_connect_by_path(test,'_'),'_') as test
FROM temp_test r2 START WITH ord = 1 -- use MIN() to get this if it's not always 1
CONNECT BY PRIOR id = id AND ord = PRIOR ord + 1;

Demo

answered Nov 19, 2018 at 17:31

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Volokh Over a year ago

Thanks, that is the fastest answer.

George Joseph · Accepted Answer · 2018-11-19 16:59:26Z

1

you can make use of recursive cte to achieve this

with cte(id,ord,test,concat_val) 
  as (select id,ord,test,test as concat_val
       from temp_test
       where ord=1
      union all
      select a.id,a.ord,a.test,b.concat_val||'_'||a.test
        from temp_test a
        join cte b
          on a.id=b.id
         and a.ord=b.ord+1 
      )
select * from cte order by id,ord

Demo here

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=78baa20f7f364e653899caf63ce7ada2

answered Nov 19, 2018 at 16:59

George Joseph

5,93213 silver badges25 bronze badges

2 Comments

Volokh Over a year ago

That provides the correct result. I will check the performance. Thanks a lot!

Volokh Over a year ago

The result is correct, but the accepted answer is about 100x faster on my data.

Collectives™ on Stack Overflow

Recursive Iteration in Oracle

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related