Combined column data display only unique value

Question

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.

Answer 1

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 

Answer 2

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