CODEWITHSUNDEEP
sqloracle-database
Boussadjra Brahim
Boussadjra Brahim

Reputation: 1

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

I have this query in oracle sql:

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

Which gives me this output...

Query Result

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

Upvotes: 0

Views: 2461

Answers (3)

Anand Singh
Anand Singh

Reputation: 210

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;

Upvotes: 1

Kris Rice
Kris Rice

Reputation: 3410

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.

Upvotes: 3

D-Shih
D-Shih

Reputation: 46249

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

Upvotes: 1

Related Questions