Repeat rows dynamically in Oracle based on a condition

Question

Heres what i have

Query

with temp as (
select 11 as x  from dual
union
select 222 as x  from dual
)
select x from temp t,(SELECT 1
          FROM DUAL 
         CONNECT BY ROWNUM <= 3)

Output

X
--
11
222
11
222
11
222

Output i desire

X
--
11
11
222    
222
222

basically i would like the row to repeat itself based on the length of the column 'X' value. so if value is 'abc' then that row should repeat 3 times

Answer 1

This works:

with temp as (
select '11' as x  from dual
union
select '222' as x  from dual
)
SELECT x, LEVEL from temp t
        CONNECT BY LEVEL <= LENGTH(t.x) 
and prior x = x
and prior sys_guid() is not null;    

Last line does the trick. Same can be achieved with other reference to PRIOR:

with temp as (
select '11' as x  from dual
union
select '222' as x  from dual
)
SELECT x, LEVEL from temp t
        CONNECT BY LEVEL <= LENGTH(t.x) 
and PRIOR x = x
and PRIOR DBMS_RANDOM.VALUE () IS NOT NULL;    

Answer 2

Since you're on 11g you can use recursive subquery factoring to achieve this:

with t as (
  select 11 as x  from dual
  union all
  select 222 as x  from dual
),
r (x, idx, len) as (
  select x, 1, length(x)
  from t
  union all
  select r.x, r.idx + 1, r.len
  from r
  where r.idx < r.len
)
select x from r
order by x;

    X
-----
   11
   11
  222    
  222
  222

The anchor member gets the original rows and the length of the value.The recursive member adds one to idx until it reaches the length.

SQL Fiddle.

You can do it with a hierarchical query too:

with t as (
  select 11 as x  from dual
  union all
  select 222 as x  from dual
)
select x
from t
connect by level <= length(x)
and prior x = x
and prior sys_guid() is not null;

The combination of the two prior clauses - one restricitng duplicates, the other involing a non-deterministic function to prevent cycling when you do that - gives you the desired rows:

    X
-----
   11
   11
  222    
  222
  222

SQL Fiddle.