This discussion is archived
13 Replies Latest reply: Nov 6, 2012 4:33 AM by AlbertoFaenza RSS

Concat rows values into single column

dbdan Newbie
Currently Being Moderated
Hi All,

How can I concat values of a multiple rows into a single column by separating them by comma.
Eg:

SELECT empno FROM emp;
empno
1
2
3
4

I want output should be:
Empnos
1,2,3,4

Thanks & Regards,
Danish
  • 1. Re: Concat rows values into single column
    jortri Explorer
    Currently Being Moderated
    For example
    WITH t AS 
      (SELECT 1 AS col FROM dual
      union
      SELECT 2 AS col FROM dual 
      union
      SELECT 3 AS col FROM dual 
      union
      SELECT 4 AS col FROM dual)
    SELECT SUBSTR(MAX(col),2) CADENA
    FROM (SELECT SYS_CONNECT_BY_PATH(col, ',') col
          FROM ( SELECT col,
                        ROW_NUMBER() OVER (ORDER BY col) FILA
                  FROM t)
          START WITH FILA = 1
          CONNECT BY PRIOR FILA = FILA - 1 )
    /
    
    CADENA
    ----------
    1,2,3,4
  • 2. Re: Concat rows values into single column
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Danish,

    That's called "String Aggregation"
    [AskTom.oracle.com|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402] shows several different ways to do it.

    I recommend the first one, the user-defined function STRAGG, which you can copy from that page.

    Once you have STRAGG installed, your query is simply
    SELECT    STRAGG (empno)     AS empnos
    FROM      emp;
    On Oracle 10 (and up) you may have a similar function, WM_CONCAT (owned by WMSYS), already installed.
    WM_CONCAT is not documented, so you may not want to use it in your Production applications.

    STRAGG is not so convenient if the order of items in the concatenated string is important.
    In that case, use XMLAGG or SYS_CONNECT_BY_PATH, as shown later in the asktom page.
    MODEL can also do ordered string aggregation.
  • 3. Re: Concat rows values into single column
    BluShadow Guru Moderator
    Currently Being Moderated
    More correctly written (for 10g and above)..
    SQL> ed
    Wrote file afiedt.buf
    
      1  WITH t AS
      2    (SELECT 1 AS col FROM dual
      3    union
      4    SELECT 2 AS col FROM dual
      5    union
      6    SELECT 3 AS col FROM dual
      7    union
      8    SELECT 4 AS col FROM dual)
      9  --
     10  SELECT LTRIM(SYS_CONNECT_BY_PATH(col, ','),',') col
     11  FROM ( SELECT col,
     12                ROW_NUMBER() OVER (ORDER BY col) FILA
     13         FROM t)
     14  WHERE CONNECT_BY_ISLEAF = 1
     15  START WITH FILA = 1
     16* CONNECT BY PRIOR FILA = FILA - 1
    SQL> /
    
    COL
    -----------------------------------------------------------
    1,2,3,4
    
    SQL>
    using CONNECT_BY_ISLEAF = 1 to pick out the leaf reaching paths rather than incorrectly using the max function.
  • 4. Re: Concat rows values into single column
    jortri Explorer
    Currently Being Moderated
    True.
    I worked now in 9.
  • 5. Re: Concat rows values into single column
    dbdan Newbie
    Currently Being Moderated
    Thanks Jortri and Blueshadow....

    It works.... I need to understand it now :)

    Regards,
    Danish
  • 6. Re: Concat rows values into single column
    MichaelS Guru
    Currently Being Moderated
    Or
    SQL>  select rtrim(xmlagg(xmlelement(e, empno || ',')).extract('//text()').extract('//text()') ,',') empnos from emp
    /
    EMPNOS                                                                          
    --------------------------------------------------------------------------------
    7369,7499,7521,7566,7654,7698,7782,7788,7839,7844,7876,7900,7902,7934  
  • 7. Re: Concat rows values into single column
    dbdan Newbie
    Currently Being Moderated
    Even this is simple.

    Thanks,
    Danish
  • 8. Re: Concat rows values into single column
    890499 Newbie
    Currently Being Moderated
    @Micheals .....Thanks alot . Yours was the best answer .
  • 9. Re: Concat rows values into single column
    946001 Newbie
    Currently Being Moderated
    Michael's Thanks a lot.
    It works for me.

    Rgds--
    Adesh
  • 10. Re: Concat rows values into single column
    972776 Newbie
    Currently Being Moderated
    hey micheal it works bt when i am using it in oracle report 6i then it gives error what should i do..:(
  • 11. Re: Concat rows values into single column
    AlbertoFaenza Expert
    Currently Being Moderated
    MichaelS wrote:
    Or
    SQL>  select rtrim(xmlagg(xmlelement(e, empno || ',')).extract('//text()').extract('//text()') ,',') empnos from emp
    /
    EMPNOS                                                                          
    --------------------------------------------------------------------------------
    7369,7499,7521,7566,7654,7698,7782,7788,7839,7844,7876,7900,7902,7934  
    Hi Michael,

    is it an error or is it correct to put extract 2 times? This is giving to me the same result:
    select rtrim(xmlagg(xmlelement(e, empno || ',')).extract('//text()'),',') empnos from emp;
    EMPNOS                                                                          
    --------------------------------------------------------------------------------
    7369,7499,7521,7566,7654,7698,7782,7788,7839,7844,7876,7900,7902,7934           
    Regards.
    Al
  • 12. Re: Concat rows values into single column
    jeneesh Guru
    Currently Being Moderated
    Wrongly replied..

    Edited by: jeneesh on Nov 6, 2012 5:41 PM

    FYI: It is a very old thread...
  • 13. Re: Concat rows values into single column
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Jeneesh,

    thx. I did not notice that it was an old thread and that 969773 has hijacked it.

    Regards.
    Al

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points