zhanzezhu zhanzezhu - 2 months ago 8
SQL Question

SQL: How return more record by recursive queries in Oracle?

data source table:

name count
a 2
b 3


expect:

a1
a2
b1
b2
b3


I want to try(but failed):

select t.name||level
from t
CONNECT BY LEVEL<=t.count

Answer

In Oracle 11g+, you can use recursive queries:

with cte(name, ind, count) as (
      select t.name, 1 as ind, t.count
      from t
      union all
      select cte.name, cte.ind + 1, cte.count
      from cte
      where cte.ind < cte.count
     )
select cte.name || cte.ind
from cte;

I prefer recursive CTEs to CONNECT BY because the former are standard and supported by most databases.

Comments