Forum Stats

  • 3,817,231 Users
  • 2,259,293 Discussions
  • 7,893,705 Comments

Discussions

Categories

Column to Row on a table (using SQL query)

CS_Ghosh
CS_Ghosh Member Posts: 2
edited Apr 1, 2015 10:12AM in SQL & PL/SQL

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.

EtbinAlbertoFaenzaCS_Ghosh
· Share on TwitterShare on Facebook

Best Answer

  • AlbertoFaenza
    AlbertoFaenza 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

    EtbinCS_Ghosh
    · Share on TwitterShare on Facebook

Answers

  • Etbin
    Etbin 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

    CS_Ghosh
    · Share on TwitterShare on Facebook
  • AlbertoFaenza
    AlbertoFaenza 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

    EtbinCS_Ghosh
    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 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.
    AlbertoFaenza
    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 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.
    CS_GhoshUser_ZUUOD
    · Share on TwitterShare on Facebook
  • AlbertoFaenza
    AlbertoFaenza Member Posts: 2,047 Silver Trophy
    edited Apr 1, 2015 8:17AM

    Hi Odie,

    on XML you are the master!!

    Interesting solution.

    Regards.

    Alberto

    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 Member Posts: 8,466 Silver Trophy
    edited Apr 1, 2015 8:19AM

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

    · Share on TwitterShare on Facebook
  • AlbertoFaenza
    AlbertoFaenza 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!

    · Share on TwitterShare on Facebook
  • CS_Ghosh
    CS_Ghosh Member Posts: 2
    edited Apr 1, 2015 10:12AM

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

    · Share on TwitterShare on Facebook
This discussion has been closed.