As you may already found that LISTAGG function is new in Oracle DB 11g. This function performs a string aggregation (concatenation) natively without implementing user-defined objects (most popular STRAGG) and other function that loops through data.
Here is the format of the function:
LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]
Here is the simple example :
SQL> select first_name from employees where department_id =20;
FIRST_NAME
——————–
Michael
Pat
SQL> select listagg(first_name,’;’) within group (order by null) as fnames
2 from employees
3 where department_id = 20;
FNAMES
——————————————————————————–
Michael;Pat
Lets say there are multiple employees whose first name is same. But I would like to concatenate those names uniquely. That means I don’t want to list duplicate names, just concatenate distinct first names.
Here is example of department which contains employees of same first name.
SQL> r
1 select listagg(first_name,’;’) within group (order by first_name) as fnames
2 from employees
3* where department_id = 80
FNAMES
——————————————————————————–
Alberto;Allan;Alyssa;Amit;Charles;Christopher;Clara;Danielle;David;David;Eleni;E
lizabeth;Ellen;Gerald;Harrison;Jack;Janette;John;Jonathon;Karen;Lindsey;Lisa;Lou
ise;Mattea;Nanette;Oliver;Patrick;Peter;Peter;Sarath;Sundar;Sundita;Tayler;Willi
am
As you see in result there are two David s and two Peter s. How can I select only distinct data.
LISTAGG function doesn’t let you use the distinct key. See below what happens if I try to do this.
SQL> r
1 select listagg(DISTINCT first_name,’;’) within group (order by first_name) as fnames
2 from employees
3* where department_id = 80
select listagg(DISTINCT first_name,’;’) within group (order by first_name) as fnames
*
ERROR at line 1:
ORA-30482: DISTINCT option not allowed for this function
But this can be resolved using sub query as below.
SQL> r
1 select listagg(first_name,’;’) within group (order by first_name) as fnames
2 from
3 (
4 select distinct department_id, first_name
5 from employees
6* where department_id = 80)
FNAMES
——————————————————————————–
Alberto;Allan;Alyssa;Amit;Charles;Christopher;Clara;Danielle;David;Eleni;Elizabe
th;Ellen;Gerald;Harrison;Jack;Janette;John;Jonathon;Karen;Lindsey;Lisa;Louise;Ma
ttea;Nanette;Oliver;Patrick;Peter;Sarath;Sundar;Sundita;Tayler;William
Now Results display distinct names as highlighted.
Oracle Enablement 