ORACLE -- SQL results in "one" CELL ?

How can I change the below SQL so it displays as ONE CELL of results, with commas between values like "CHE, HAZ, PAI" instead of three separate cells ?

select substr(test,2,555) Types
from jobs
where min_salary=1 and substr(test,1,1)='^'
--------------------------------------------------------
CREATE TABLE "JOBS"
   ("JOB_ID" VARCHAR2(10),
      "JOB_TITLE" VARCHAR2(35),
      "MIN_SALARY" NUMBER(6,0),
      "MAX_SALARY" NUMBER(6,0),
      "TEST" VARCHAR2(5)
   ) ;
---------------------------------------------------
Insert into JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY,TEST) values ('AD_PRES','President, "ceo"',1,40000,'^CHE');
Insert into JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY,TEST) values ('AD_VP','Administration Vice President',1,30000,'^HAZ');
Insert into JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY,TEST) values ('AD_ASST','Administration Assistant',1,6000,'^PAI');
Insert into JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY,TEST) values ('FI_MGR','Finance Manager',1,16000,null);
Insert into JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY,TEST) values ('FI_ACCOUNT','Accountant',1,9000,null);
Insert into JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY,TEST) values ('AC_MGR','Accounting Manager',1,16000,null);
finance_teacherAsked:
Who is Participating?
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
use this

with t  as (
select 1 idx,'^ABC' title from dual
union
select 1 idx,'^XVT' title from dual
union
select 1 idx,'^UNT' title from dual
)
select rtrim(xmlagg (xmlelement (e, replace(title, '^','')|| ',')).extract ('//text()'), ', ') test_result
from t

test_result
ABC, XVT, UNT
select rtrim(xmlagg (xmlelement (e, replace(test, '^','')|| ',')).extract ('//text()'), ', ') test_result
from jobs 
where min_salary=1 and substr(test,1,1)='^'

Open in new window

0
 
awking00Connect With a Mentor Commented:
A slight variation -
select translate(rtrim(extract(xmlagg(xmlelement("x",test||',')),'/x/text()').getstringval(),','),'X^','X') test
from jobs;
Also note that, if you're going to select other columns, you will need to group by those columns.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.