CODEWITHSUNDEEP
oracle10g
user2646241
user2646241

Reputation: 3

Concatenate single column from multiple rows into single column seperated by ',' in Oracle 10g?

I have the table EMP, in RDBMS oracle 10g and i would like get the output as follows

EMPNO   ENAME      Concat_column

7369    SMITH      SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
7499    ALLEN
7521    WARD
7566    JONES
7654    MARTIN
7698    BLAKE
7782    CLARK
7788    SCOTT
7839    KING
7844    TURNER
7876    ADAMS
7900    JAMES
7902    FORD
7934    MILLER

Please help me.

Thank you in advance and Sorry for my English and typing mistakes.

Upvotes: 0

Views: 482

Answers (2)

Gaston Flores
Gaston Flores

Reputation: 2467

Try with this query, maybe will be useful:

SELECT rtrim (xmlagg (xmlelement (e, y.NAME || ',')).extract ('//text()'), ',') Concat_column
FROM EMP y

You can try this here (SQL Fiddle).

Second version, maybe this is exactly what you want:

SELECT a.EMPNO, a.NAME, b.Concat_column
FROM EMP a
LEFT JOIN (
  SELECT 
    7369 AS EMPNO, 
    'SMITH' AS NAME,
    rtrim (xmlagg (xmlelement (e, y.NAME || ',')).extract ('//text()'), ',') AS Concat_column
  FROM EMP y 
) b ON a.EMPNO = b.EMPNO

Link to SQL Fiddle.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231651

Tim Hall has a page that lists a variety of string aggregation techniques.

I personally prefer the user-defined aggregate function but there are a number of other techniques that work as well.

Upvotes: 0

Related Questions