# 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