## Forum Stats

• 3,817,231 Users
• 2,259,293 Discussions

Discussions

# Column to Row on a table (using SQL query)

Member Posts: 2
edited Apr 1, 2015 10:12AM

Data in my table looks like this -

Table Data

===============

COL_1    COL_2

----------    ----------

1             ABCD

2             ABC

3             ABCDE

4             AB

5             ABCDEFGH

Now the requirement is, for any of the row, length of the text in COL_2 exceeds 3, display the remaining text in new line. For example, the first row (COL_1 = 1), length of COL_2 is 4, so, in SQL output two rows will be displayed as given below. Also, for the last row (COL_1 = 5), length is 8, so, in SQL output 3 rows will be displayed like below. And, for COL_1 = 2 & 4, only 1 row will be displayed as the length of COL_2 text is less or equal to 3.

OUTPUT

===============

COL_1    COL_2

----------    ----------

1             ABC

1             D

2             ABC

3             ABC

3             DE

4             AB

5             ABC

5             DEF

5             GH

We need to implement it using SQL only.

• Member Posts: 2,047 Silver Trophy
edited Apr 1, 2015 6:25AM Answer ✓

Hi,

the solution from etbin uses Recursive Subquery Factoring which is available from Oracle 11gR2.

Since you did not mention your version I'm posting here 2 alternative solutions using CONNECT BY and Xquery:

```with mydata as
(
select 1 c1,'ABCD'     c2  from dual union all
select 2   ,'ABC'          from dual union all
select 3   ,'ABCDE'        from dual union all
select 4   ,'AB'           from dual union all
select 5   ,'ABCDEFGH'     from dual
)
select c1, substr (c2,level*3-2, 3) as c2
from mydata
connect by substr (c2,level*3-2, 3) is not null
and c1 = prior c1
and prior dbms_random.random is not null;

C1 C2
---------- ---
1 ABC
1 D
2 ABC
3 ABC
3 DE
4 AB
5 ABC
5 DEF
5 GH

9 rows selected.

with mydata as
(
select 1 c1,'ABCD'     c2  from dual union all
select 2   ,'ABC'          from dual union all
select 3   ,'ABCDE'        from dual union all
select 4   ,'AB'           from dual union all
select 5   ,'ABCDEFGH'     from dual
)
select a.c1, x.c2
from mydata a
, xmltable('ora:tokenize(\$c2, ",")'
passing regexp_replace(a.c2, '(.{1,3})','\1,')  as "c2"
columns c2 varchar2(30) path '.'
) x
where x.c2 is not null;

C1 C2
---------- ------------------------------
1 ABC
1 D
2 ABC
3 ABC
3 DE
4 AB
5 ABC
5 DEF
5 GH

9 rows selected.

```

Edit: corrected the Xquery solution by using a simplified regexp_replace.

Regards.

Alberto

• Member Posts: 8,968 Gold Crown
edited Apr 1, 2015 4:15AM

with

data(col_1,col_2,col_3) as

(select c1,substr(c2,1,3),substr(c2,4)

from (select 1 c1,'ABCD' c2 from dual union all

select 2,'ABC' from dual union all

select 3,'ABCDE' from dual union all

select 4,'AB' from dual union all

select 5,'ABCDEFGH' from dual

)

union all

select col_1,substr(col_3,1,3),substr(col_3,4)

from data

where col_3 is not null

)

select col_1,col_2

from data

order by col_1,col_2

COL_1COL_2
1ABC
1D
2ABC
3ABC
3DE
4AB
5ABC
5DEF
5GH

Regards

Etbin

• Member Posts: 2,047 Silver Trophy
edited Apr 1, 2015 6:25AM Answer ✓

Hi,

the solution from etbin uses Recursive Subquery Factoring which is available from Oracle 11gR2.

Since you did not mention your version I'm posting here 2 alternative solutions using CONNECT BY and Xquery:

```with mydata as
(
select 1 c1,'ABCD'     c2  from dual union all
select 2   ,'ABC'          from dual union all
select 3   ,'ABCDE'        from dual union all
select 4   ,'AB'           from dual union all
select 5   ,'ABCDEFGH'     from dual
)
select c1, substr (c2,level*3-2, 3) as c2
from mydata
connect by substr (c2,level*3-2, 3) is not null
and c1 = prior c1
and prior dbms_random.random is not null;

C1 C2
---------- ---
1 ABC
1 D
2 ABC
3 ABC
3 DE
4 AB
5 ABC
5 DEF
5 GH

9 rows selected.

with mydata as
(
select 1 c1,'ABCD'     c2  from dual union all
select 2   ,'ABC'          from dual union all
select 3   ,'ABCDE'        from dual union all
select 4   ,'AB'           from dual union all
select 5   ,'ABCDEFGH'     from dual
)
select a.c1, x.c2
from mydata a
, xmltable('ora:tokenize(\$c2, ",")'
passing regexp_replace(a.c2, '(.{1,3})','\1,')  as "c2"
columns c2 varchar2(30) path '.'
) x
where x.c2 is not null;

C1 C2
---------- ------------------------------
1 ABC
1 D
2 ABC
3 ABC
3 DE
4 AB
5 ABC
5 DEF
5 GH

9 rows selected.

```

Edit: corrected the Xquery solution by using a simplified regexp_replace.

Regards.

Alberto

• Member Posts: 8,466 Silver Trophy
edited Apr 1, 2015 7:45AM
```Since you did not mention your version I'm posting here 2 alternative solutions using CONNECT BY and Xquery:
```

ora:tokenize is from 11.2 too.

But going for the XQuery approach, I wouldn't use it at all.

There's no need to generate a csv just to split it immediately after, a simple substring operation will do fine :

```SQL> with sample_data (c1, c2) as (
2     select 1, 'ABCD'    from dual union all
3     select 2, 'ABC'     from dual union all
4     select 3, 'ABCDE'   from dual union all
5     select 4, 'AB'       from dual union all
6     select 5, 'ABCDEFGH' from dual union all
7     select 6, '' from dual
8  )
9  select t.c1, x.c2
10  from sample_data t
11     , xmltable(
12         'for \$i in 0 to xs:integer(ceiling(string-length(\$str) div 3) - 1)
13          return substring(\$str, 1+3*\$i, 3)'
14         passing t.c2 as "str"
15         columns c2 varchar2(3) path '.'
16       ) (+) x
17  ;

C1 C2
---------- ---
1 ABC
1 D
2 ABC
3 ABC
3 DE
4 AB
5 ABC
5 DEF
5 GH
6

10 rows selected.
```
• Member Posts: 8,466 Silver Trophy
edited Apr 1, 2015 8:07AM

For fun, the XQuery implementation of the recursive approach :

```SQL> with sample_data (c1, c2) as (
2     select 1, 'ABCD'    from dual union all
3     select 2, 'ABC'     from dual union all
4     select 3, 'ABCDE'   from dual union all
5     select 4, 'AB'       from dual union all
6     select 5, 'ABCDEFGH' from dual union all
7     select 6, '' from dual
8  )
9  select t.c1, x.c2
10  from sample_data t
11     , xmltable(
12         'declare function local:split(\$str as xs:string?, \$n as xs:integer) as xs:string*
13          {
14            if (\$str != "")
15              then (substring(\$str, 1, \$n), local:split(substring(\$str, \$n + 1), \$n))
16              else ()
17          };
18          local:split(\$c2, xs:int(\$size))'
19         passing t.c2 as "c2"
20               , 2 as "size"
21         columns c2 varchar2(3) path '.'
22       ) (+) x
23  ;

C1 C2
---------- ---
1 AB
1 CD
2 AB
2 C
3 AB
3 CD
3 E
4 AB
5 AB
5 CD
5 EF
5 GH
6

13 rows selected.
```
• Member Posts: 2,047 Silver Trophy
edited Apr 1, 2015 8:17AM

Hi Odie,

on XML you are the master!!

Interesting solution.

Regards.

Alberto

• Member Posts: 8,466 Silver Trophy
edited Apr 1, 2015 8:19AM

I don't see any XML involved here. Do you?

• Member Posts: 2,047 Silver Trophy
edited Apr 1, 2015 8:37AM
```odie_63 wrote:

I don't see any XML involved here. Do you?
```

That's correct!! Shame on me!

• Member Posts: 2
edited Apr 1, 2015 10:12AM

Thank you Etbin, Alberto and Odie. You guys rock.

This discussion has been closed.