Oracle : Combine unique row values into comma-separated string

Question

I have a query that returns data like below, need to combine the value column into comma-separated string excluding duplicates and null values.

g_name    g_id      v_data    
-----     ----      ------
Test      123        ABC
Test      123        ABC
Test      123        DEG
Test      123        None
Test      123        
Test      123        HIJ

Desired output :

g_name    g_id      v_data    
-----     ----      ------
Test      123        ABC,DEG,HIJ

I have tried using XMLAGG but can't remove duplicates and null values.

select g_name,
     g_id,
     RTRIM(XMLAGG(XMLELEMENT(e, v_data || ',')).EXTRACT('//text()'), ',')
from tblData
group by g_name, g_id

score 1 · Answer 1 · answered Aug 29 '14 at 07:14

1

You can use the LISTAGG function:

select g_name, g_id, listagg(v_data,',') within group (order by v_data) v_data
from (
  select distinct g_name, g_id, v_data
  from tblData
  where v_data is not null
)
group by g_name, g_id

In the inner select, we deal with the DISTINCT and NULL removal (you could also remove None here if you wanted).
The outer query deals with grouping by your name and id and concatenating the values

answered Aug 29 '14 at 07:14

Captain

2,148
15
13

LISTAGG is only available Oracle 11g, and im using Oracle 10g :( – Ianthe Aug 29 '14 at 08:04

score 1 · Accepted Answer · answered Aug 29 '14 at 09:38

Just pre-filter your rows with a common table expression, then do your string concatenation.

with cte1 as (
    select distinct *
    from tblData
    where v_data is not null
)
select g_name,
    g_id,
    RTRIM(XMLAGG(XMLELEMENT(e, v_data || ',')).EXTRACT('//text()'), ',')
from cte1
group by g_name, g_id

Oracle : Combine unique row values into comma-separated string

2 Answers2