Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Concat rows values into single column

dbdanJun 25 2009 — edited Nov 6 2012
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

Comments

jortri
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
Frank Kulash
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.
BluShadow
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.
jortri
True.
I worked now in 9.
dbdan
Thanks Jortri and Blueshadow....

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

Regards,
Danish
MichaelS
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
dbdan
Even this is simple.

Thanks,
Danish
890499
@Micheals .....Thanks alot . Yours was the best answer .
946001
Michael's Thanks a lot.
It works for me.

Rgds--
Adesh
972776
hey micheal it works bt when i am using it in oracle report 6i then it gives error what should i do..:(
AlbertoFaenza
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
jeneesh
Wrongly replied..

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

FYI: It is a very old thread...
AlbertoFaenza
Hi Jeneesh,

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

Regards.
Al
1 - 13
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 4 2012
Added on Jun 25 2009
13 comments
74,107 views