1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Out put in single row

Discussion in 'Server Administration and Options' started by Tariq Bashir Malhi, Dec 2, 2010.

  1. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    I have a single column table, having different alpha codes i.e AB,AC,AD e.t.c, i need output in following way

    'AB','AC','AD'...........

    instead of

    AB
    AC
    AD
    .
    .
    .
    .

    How i can manage it............?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
     
    SQL> CREATE TABLE test(col1 varchar2(2));
    TABLE created.
    SQL>
    SQL> INSERT ALL
      2  INTO test
      3  VALUES ('AA')
      4  INTO test
      5  VALUES ('AB')
      6  INTO test
      7  VALUES ('AC')
      8  INTO test
      9  VALUES ('AD')
     10  INTO test
     11  VALUES ('AE')
     12  INTO test
     13  VALUES ('AF')
     14  INTO test
     15  VALUES ('AG')
     16  INTO test
     17  VALUES ('AH')
     18  INTO test
     19  VALUES ('AI')
     20  SELECT * FROM dual;
    9 ROWS created.
    SQL>
    SQL> SELECT wm_concat(''''||col1||'''') colstrg
      2  FROM test;
    COLSTRG
    --------------------------------------------------------------------------------
    'AA','AB','AC','AD','AE','AF','AG','AH','AI'
    SQL>

     

    Such examples are posted on the web and also in this forum; a search would have produced them.
     
  3. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    WM_CONCAT is available in WMSYS user and also it is undocumented form.
    You can achieve this way of display using XMLAGG function and LISTAGG analytic function (introduced in 11g).

    Code (SQL):
    SELECT department_id, rtrim (xmlagg (xmlelement (e, last_name || ',')).EXTRACT ('//text()'), ',') names
    FROM employees
    GROUP BY    department_id;
    You can find other examples too on web
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    How to achieve the same if the people are using Oracle versions like 9i ???
     
  5. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Kiran,
    XMLAGG and WM_CONCAT is available in oracle 9i version. LISTAGG is the newly introduced analytic function in oracle 11g