How to convert row columns to comma-delimited string in sql oracle

Question

I have this query in oracle sql:

SELECT * FROM HR.EMPLOYEES emp WHERE EMP.EMPLOYEE_ID=100;

Which gives me this output...

I want to get this result (column values comma-delimited)...

100,Steven,King,SKING,515.123.4567,17/06/03,AD_PRES,29000,105,90

There are tools that can do this, such as SQL Developer. Or, you can write your own generic converter (like this one). Or for a specific query you can concatenate the values with ||. You may need to consider formats for things like dates and timestamps. — William Robertson
– William Robertson, Commented Apr 6, 2018 at 12:58

Kris Rice · Accepted Answer · 2018-04-06 15:43:16Z

In SQLDeveloper and in SQLcl, the format can just be set to csv and then issue sql as normal. http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html

The setsqlformat has multiple options such as csv and insert

KLRICE@xe🥧>help set sqlformat
 SET SQLFORMAT {          csv,html,xml,json,ansiconsole,insert,loader,fixed,default}   

KLRICE@xe🥧>    
KLRICE@xe🥧>set sqlformat csv
KLRICE@xe🥧>select * from emp;
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7839,"KING","PRESIDENT",,17-NOV-81,5000,,10
7698,"BLAKE","MANAGER",7839,01-MAY-81,2850,,30
7782,"CLARK","MANAGER",7839,09-JUN-81,2450,,10
7566,"JONES","MANAGER",7839,02-APR-81,2975,,20
7788,"SCOTT","ANALYST",7566,19-APR-87,3000,,20
7902,"FORD","ANALYST",7566,03-DEC-81,3000,,20
7369,"SMITH","CLERK",7902,17-DEC-80,800,,20
7499,"ALLEN","SALESMAN",7698,20-FEB-81,1600,300,30
7521,"WARD","SALESMAN",7698,22-FEB-81,1250,500,30
7654,"MARTIN","SALESMAN",7698,28-SEP-81,1250,1400,30
7844,"TURNER","SALESMAN",7698,08-SEP-81,1500,0,30
7876,"ADAMS","CLERK",7788,23-MAY-87,1100,,20
7900,"JAMES","CLERK",7698,03-DEC-81,950,,30
7934,"MILLER","CLERK",7782,23-JAN-82,1300,,10

14 rows selected. 

KLRICE@xe🥧>set sqlformat insert
KLRICE@xe🥧>/
REM INSERTING into EMP
SET DEFINE OFF;
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_timestamp('17-NOV-81','DD-MON-RR HH.MI.SSXFF AM'),5000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_timestamp('01-MAY-81','DD-MON-RR HH.MI.SSXFF AM'),2850,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_timestamp('09-JUN-81','DD-MON-RR HH.MI.SSXFF AM'),2450,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_timestamp('02-APR-81','DD-MON-RR HH.MI.SSXFF AM'),2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_timestamp('19-APR-87','DD-MON-RR HH.MI.SSXFF AM'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_timestamp('03-DEC-81','DD-MON-RR HH.MI.SSXFF AM'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_timestamp('17-DEC-80','DD-MON-RR HH.MI.SSXFF AM'),800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_timestamp('20-FEB-81','DD-MON-RR HH.MI.SSXFF AM'),1600,300,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_timestamp('22-FEB-81','DD-MON-RR HH.MI.SSXFF AM'),1250,500,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_timestamp('28-SEP-81','DD-MON-RR HH.MI.SSXFF AM'),1250,1400,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_timestamp('08-SEP-81','DD-MON-RR HH.MI.SSXFF AM'),1500,0,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_timestamp('23-MAY-87','DD-MON-RR HH.MI.SSXFF AM'),1100,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_timestamp('03-DEC-81','DD-MON-RR HH.MI.SSXFF AM'),950,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_timestamp('23-JAN-82','DD-MON-RR HH.MI.SSXFF AM'),1300,null,10);

14 rows selected.

or just do SELECT /*csv*/ * FROM HR.EMPLOYEES emp WHERE EMP.EMPLOYEE_ID=100; -- execute with F5
but i need to get the generated string in order to cast it and insert it into another table
@boussadjra how do you mean 'cast it?' There is also an /*insert*/ mode.
i need something like: insert into another_table values (cast(select /*csv*/ * from mycurrent_table), /* additional_values */);

D-Shih · Accepted Answer · 2018-04-06 12:58:58Z

1

You need to use || operator and , get your result.

SELECT EMPLOYEE_ID || ',' || First_Name || Last_NAME|| ',' || PHONE_NUMBER||','||JOB_ID ||','||SALARY||','||MANAGER_ID||','||DEPARTMENT_ID
FROM EMPLOYEES  
WHERE EMPLOYEE_ID=100;

http://www.sqlfiddle.com/#!4/277d5/2

edited Apr 6, 2018 at 12:58

answered Apr 6, 2018 at 12:50

D-Shih

46.5k6 gold badges38 silver badges56 bronze badges

Comments

wwkudu · Accepted Answer · 2018-04-09 12:36:15Z

1

You can use the following query:

SELECT EMPLYEE_ID || ',' || First_Name || ',' || Last_NAME || ',' ||
  PHONE_NUMBER ||','|| JOB_ID ||','|| SALARY ||',' || 
  MANAGER_ID || ',' || DEPARTMENT_ID
FROM HR.EMPLOYEES emp 
WHERE EMP.EMPLOYEE_ID=100;

edited Apr 9, 2018 at 12:36

wwkudu

2,7963 gold badges31 silver badges42 bronze badges

answered Apr 6, 2018 at 12:54

Anand Singh

2102 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to convert row columns to comma-delimited string in sql oracle

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related