Forum Stats

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

Discussions

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

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

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

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

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

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

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

This discussion has been closed.