0

This is my source table. I want to combined column data and display unique data only.

SQL> SELECT * FROM DUMMY;

PLAN_CD              PLAN_NM              CLASS
-------------------- -------------------- ------
D01501               DENTAL               AA
D01501               DENTAL               AB
D01501               DENTAL               AC
V01501               VISION               AA
V01501               VISION               AB

And i want to ouput like this.

PLAN_CD              PLAN_NM              
-------              --------
D01501               DENTAL,AA,AB,AC              
V01501               VISION,AA,AB

I am using oracle 10g.Please give me query for oracle 10g.

Thanks in advance.

0

2 Answers 2

3

There is no easy and supported built-in method for string aggregation in 10g. (Do not use WM_CONCAT, as it is not supported, and it is not available anymore in 12c.)

You need to write your own string aggregation function, for example:

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
/

CREATE FUNCTION to_string (
                nt_in        IN varchar2_ntt,
                delimiter_in IN VARCHAR2 DEFAULT ','
                ) RETURN VARCHAR2 IS

   v_idx PLS_INTEGER;
   v_str VARCHAR2(32767);
   v_dlm VARCHAR2(10);

BEGIN

   v_idx := nt_in.FIRST;
   WHILE v_idx IS NOT NULL LOOP
      v_str := v_str || v_dlm || nt_in(v_idx);
      v_dlm := delimiter_in;
      v_idx := nt_in.NEXT(v_idx);
   END LOOP;

   RETURN v_str;

END to_string;
/

Source is: http://www.oracle-developer.net/display.php?id=306

Your sample data:

create table dummy
(
  plan_cd varchar2(6 char),
  plan_nm varchar2(6 char),
  class varchar2(2 char)
);

insert into dummy values ('D01501', 'DENTAL', 'AA');
insert into dummy values ('D01501', 'DENTAL', 'AB');
insert into dummy values ('D01501', 'DENTAL', 'AC');
insert into dummy values ('V01501', 'VISION', 'AA');
insert into dummy values ('V01501', 'VISION', 'AB');
commit;

And finally the query:

select
  plan_cd,
  plan_nm || ',' 
  || to_string(cast(collect(class order by class) as varchar2_ntt)) as plan_nm
from
  dummy
group by
  plan_cd, plan_nm
;

PLAN_CD PLAN_NM                                
------- ----------------------------------------
D01501  DENTAL,AA,AB,AC                         
V01501  VISION,AA,AB    
0
1

In 9i and 10g, You can use XMLAGG function for string aggregation with this little trick as shown by Donald K. Burleson

select 
   (select department_name from HR.DEPARTMENTS where department_id=MTE.department_id) "DEPARTMENT_NAME", 
   (select first_name from hr.employees where employee_id = MTE.manager_id) "MANAGER_NAME",
   rtrim (xmlagg (xmlelement (e, first_name || ', ')).extract ('//text()'), ', ') "EMPLOYEES"
from 
   HR.EMPLOYEES MTE
group by 
   department_id, 
   manager_id
;

XMLELEMENT: Produces an XML element "e" from each "first_name" row concatenated with a comma
XMLAGG: Aggregates XML elements into a single row
EXTRACT member function: Extracts xml values in varchar2()
RTRIM: Removes last comma

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.