• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 656
  • Last Modified:

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);
0
finance_teacher
Asked:
finance_teacher
2 Solutions
 
HainKurtSr. 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
 
awking00Commented:
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now