Search code examples
sqloracle-databaseoracle11gstring-concatenation

Oracle query to concat comma separated column values

I have a table employee having columns Name, Age, Department.

I need a query which produce output which concat values of Name , Age, Department as comma separated.

Query output should be like below :

ABC,23,Science
XYZ,34,Bio
QQQ,22,Account

I am not using stored procedure.

I search on internet and found concat function but looks like it wont work on multiple columns. Pls help considering i have 1-5 million records in table so need to look performance point as well.

Solution

  • CONCAT doesn't behave nicely in this context as you have to nest several functions. But, the good, old double pipe concatenation operator || works well:

    SQL> select ename ||','|| sal ||','|| job result
  2  from emp
  3  where rownum < 5;

RESULT
---------------------------------------------------
SMITH,1000,CLERK
ALLEN,1600,SALESMAN
WARD,1250,SALESMAN
JONES,2975,MANAGER

SQL>