# 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

+3

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;
```

```
+5

source

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
```

```
+2

source

``````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)
order by 1
```

```
-1

source

All Articles