4 Replies Latest reply: Jan 18, 2013 1:00 AM by 985488 RSS

    output row in vertical format but in seperate rows

    985488
      Hi,

      I have a static data as below,

      '2811009',
      '3421905',
      '3446304',
      '3457608',
      '3822608',
      '5565505',
      '4567890',
      '6229207',
      '6742001',
      '7206502'

      I want this in below format

      2811009 - Row 1
      3421905 - Row 2
      3446304 - Row 3
      3457608 - Row 4
      3822608 - Row 5

      Please help me.
        • 1. Re: output row in vertical format but in seperate rows
          jeneesh
          One way
          with static_data as
          (
          select q'[''2811009',
          '3421905',
          '3446304',
          '3457608',
          '3822608',
          '5565505',
          '4567890',
          '6229207',
          '6742001',
          '7206502']' str
          from dual
          )
          select  regexp_substr(str,'''([^'']+)''',1,level,null,1) str
          from static_data 
          connect by regexp_substr(str,'''([^'']+)''',1,level,null,1) is not null;
          
          STR        
          -----------
          2811009      
          3421905      
          3446304      
          3457608      
          3822608      
          5565505      
          4567890      
          6229207      
          6742001      
          7206502      
          
           10 rows selected 
          • 2. Re: output row in vertical format but in seperate rows
            Suman Rana
            Another solution...

            SELECT val || ' - Row ' || ROWNUM Val
            FROM ( SELECT COLUMN_VALUE Val
                           FROM TABLE (
                                     SYS.ODCIVARCHAR2LIST ('3421905',
                                                              '3446304',
                                                              '3457608',
                                                              '3822608',
                                                              '5565505',
                                                              '4567890',
                                                              '6229207',
                                                              '6742001',
                                                              '7206502',
                                                              '2811009'))
                      ORDER BY 1)

            Output:

            VAL
            ______________

            2811009 -Row 1
            3421905 -Row 2
            3446304 -Row 3
            3457608 -Row 4
            3822608 -Row 5
            4567890 -Row 6
            5565505 -Row 7
            6229207 -Row 8
            6742001 -Row 9
            7206502 -Row 10

            Edited by: Suman Rana on Jan 17, 2013 7:29 PM

            Edited by: Suman Rana on Jan 17, 2013 7:29 PM

            Edited by: Suman Rana on Jan 17, 2013 7:30 PM
            • 3. Re: output row in vertical format but in seperate rows
              985488
              Thnx jeneesh.
              Solution for the query is Correct.

              Edited by: 982485 on Jan 17, 2013 10:58 PM