4 Replies Latest reply: Oct 3, 2012 6:08 AM by BluShadow RSS

    Convert Single Column into Multiple Rows

    894347
      Hi Folks,

      I am having a task where need to display a single column into multiple rows(to be used in LOV)

      For Ex:

      The column consistes of value as 98,78,67,68,34,90. --- This is a single column values where no of values may be 'n' number which is seperated by commas

      Then need to display it as

      98
      78
      67
      68
      34
      90
      --- as many rows (No of rows may be 'n' number).

      Thanks in Advance
        • 1. Re: Convert Single Column into Multiple Rows
          Ashu_Neo
          Try this..
          SQL> ed
          Wrote file afiedt.buf
          
            1  select regexp_substr('98,78,67,68,34,90', '[^,]+',1,level) Value
            2    from dual
            3*   connect by level <= regexp_count('98,78,67,68,34,90',',') + 1
          SQL> /
          
          VALUE
          -----------------
          98
          78
          67
          68
          34
          90
          
          6 rows selected.
          Thanks!
          • 2. Re: Convert Single Column into Multiple Rows
            908002
            select * from table( sys.dbms_debug_vc2coll(98,78,67,68,34,90));
            • 3. Re: Convert Single Column into Multiple Rows
              Frank Kulash
              Hi,

              If you have only one string:
              WITH     sample_data     AS
              (
                   SELECT  '98,78,67,68,34,90'     AS str
                   FROM     dual
              )
              SELECT     LEVEL               AS N
              ,     REGEXP_SUBSTR ( str
                              , '[^,]+'
                              , 1
                              , LEVEL
                              )          AS sub_str
              FROM     sample_data
              CONNECT BY     LEVEL     <= 1 + LENGTH (str)
                                  - LENGTH (REPLACE (str, ','))
              ;
              This works in Orcle 10.1 and up.
              Starting in Oracle 11.1, you can use REGEXP_COUNT to find how many ','s are in str.
              • 4. Re: Convert Single Column into Multiple Rows
                BluShadow
                You may also consider fixing your data model so that you are not storing relational data in a non-relational manner. i.e. storing multiple values in a single value column is not a good idea.