ORACLE -- SQL results in "one" CELL ?

Posted on 2011-04-19
Last Modified: 2013-12-18
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)='^'
   ("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);
Question by:finance_teacher
    LVL 51

    Accepted Solution

    use this

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

    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

    LVL 31

    Assisted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Oracle Query 13 58
    oracle- 10.2.04 3 33
    Value of 0's not appearing. 9 40
    Passing list of object to Oracle Database Procedure 3 30
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now