Repeat rows dynamically in Oracle based on condition
This is what I have
Request
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
Conclusion of the desired
X
--
11
11
222
222
222
Basically, I would like the string to repeat itself based on the length of the column "X" value. so if the value is 'abc' then this string must repeat 3 times
source to share
It 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;
The last line does the trick. The same can be achieved with another link 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;
source to share
Since you are using 11g, you can use a recursive factoring subquery 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 binding element gets the original strings and the length of the value. A recursive member adds one to idx
until it reaches length.
You can do this with a hierarchical query as well:
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;
Combining the two sentences prior
- one is a duplicate, the other is a non-deterministic function to prevent looping as you do this - gives you the lines you want:
X
-----
11
11
222
222
222
source to share