CODEWITHSUNDEEP
sqloracle-database
zhanzezhu
zhanzezhu

Reputation: 121

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

Upvotes: 0

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

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.

Upvotes: 1

FreeMan
FreeMan

Reputation: 1457

You did well, but you should add group by clause to upper select statement.

select d from 
(select (t.NAME || level)d
  from  Test t
CONNECT BY LEVEL <= t.CT)
group by d
order by d

works well.

Upvotes: 0

Related Questions