9 Replies Latest reply: Nov 26, 2012 7:34 AM by BluShadow RSS

    single colunm convert to multiple rows

    suniljai
      Hi

      my requirement is

      create table t1(col1 varchar2(20));

      insert into t1 values('a,b,c,d,e');

      my output:
      ----------
      a
      b
      c
      d
      e

      please using simple sql query


      THANKS
      sunil.p
        • 2. Re: single colunm convert to multiple rows
          Most Wanted!!!!
           
          INSERT INTO r_test
               VALUES ('a');
          
          INSERT INTO r_test
               VALUES ('b');
          
          INSERT INTO r_test
               VALUES ('c');
          
          INSERT INTO r_test
               VALUES ('d');
          
          INSERT INTO r_test
               VALUES ('e');
          
          
          SELECT *
            FROM r_test;
            
          output
          a
          b
          c
          d
          e
          regards ,
          friend

          Edited by: most wanted!!!! on Nov 26, 2012 4:21 AM
          • 3. Re: single colunm convert to multiple rows
            Hoek
            Welcome to the forum.

            Here's an idea:
            SQL> create table t1(col1 varchar2(20));
            
            Table created.
            
            SQL> 
            SQL> insert into t1 values('a,b,c,d,e');
            
            1 row created.
            
            SQL> 
            SQL> select regexp_substr (col1, '[^,]+', 1, rn) output
              2  from   t1
              3  cross join (select rownum rn
              4              from ( select max (length (regexp_replace (col1, '[^,]+')))+1 mx
              5                     from   t1
              6                   )
              7              connect by level <= mx
              8             )
              9  where regexp_substr (col1, '[^,]+', 1, rn) is not null;
            
            OUTPUT
            --------------------------------------------------------------------------------
            a
            b
            c
            d
            e
            See:
            http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2189860818012
            http://nuijten.blogspot.nl/2011/08/splitting-comma-delimited-string-regexp.html
            http://nuijten.blogspot.nl/2009/07/splitting-comma-delimited-string-regexp.html
            for more examples and check out the SQL and PL/SQL FAQ as well:
            SQL and PL/SQL FAQ

            Edited by: Hoek on Nov 26, 2012 1:41 PM fixed asktom link
            • 4. Re: single colunm convert to multiple rows
              ranit B
              This is it...
              with xx as(
                  select 'a,b,c,d,e' str from dual 
              )
              select level,regexp_substr(str,'[^,]+',1,level) from xx
              connect by level<= regexp_count(str,',')+1;
              gives
              1     a
              2     b
              3     c
              4     d
              5     e
              This is very simple & a good way to use the powerful REGEX.

              Edited by: ranit B on Nov 26, 2012 6:12 PM
              -- o/p added
              • 5. Re: single colunm convert to multiple rows
                BluShadow
                About the simplest way I know...
                SQL> create table t1(col1 varchar2(20));
                
                Table created.
                
                SQL> insert into t1
                  2  select regexp_substr('a,b,c,d,e','[^,]+',1,level)
                  3  from   dual
                  4  connect by regexp_substr('a,b,c,d,e','[^,]+',1,level) is not null;
                
                5 rows created.
                
                SQL> select * from t1;
                
                COL1
                --------------------
                a
                b
                c
                d
                e
                • 6. Re: single colunm convert to multiple rows
                  Hoek
                  ranit B wrote:
                  This is it...
                  If OP has only one record in the table...then it would be sufficient. If there are multiple records, then it would not be.
                  • 7. Re: single colunm convert to multiple rows
                    ranit B
                    Hoek wrote:
                    ranit B wrote:
                    This is it...
                    If OP has only one record in the table...then it would be sufficient. If there are multiple records, then it would not be.
                    Now, Is this fine...?
                    with xx as(
                        select 'a,b,c,d,e' str from dual union all
                        select 'f,g,h' str from dual  union all
                        select 'i,j,k' str from dual  
                    )
                    select distinct regexp_substr(str,'[^,]+',1,level) from xx
                    connect by level<= regexp_count(str,',')+1;
                    gives
                    h
                    e
                    k
                    i
                    d
                    j
                    g
                    b
                    a
                    c
                    f
                    • 8. Re: single colunm convert to multiple rows
                      Hoek
                      It would depend on the exact requirement.
                      Ofcourse there's more to it.
                      Usually there's a need for an ORDER BY or some GROUPING on a splitted set of data.
                      Usually the need to split strings indicates some flaw in the datamodel or a 'fix' for a 'flat interface' into relational storage of data.
                      But OP can also have meant to ask his/her question in an 'academic/theoretical' way.
                      Just my $0.02 ;)
                      • 9. Re: single colunm convert to multiple rows
                        BluShadow
                        Hoek wrote:
                        It would depend on the exact requirement.
                        Exactly, though the OP has only specified he wishes to insert a single comma delimited string.
                        Ofcourse there's more to it.
                        Usually there's a need for an ORDER BY or some GROUPING on a splitted set of data.
                        Not with an insert.... ordering isn't important when inserting data.
                        Usually the need to split strings indicates some flaw in the datamodel or a 'fix' for a 'flat interface' into relational storage of data.
                        Absolutely.
                        But OP can also have meant to ask his/her question in an 'academic/theoretical' way.
                        Yep, we can only answer what is asked.