CODEWITHSUNDEEP
sqloracle-databaselistagg
Cindy
Cindy

Reputation: 135

Merging multiple rows and column data into single concatenated row

Here's how my table looks

ID Name1 Name2 Name3
1  a     b       c 
1  c     d       a 
2  d     e       a 
2  c     d       b

I need one row per ID having distinct name1,name2,name3 in one row as a comma separated string.

ID Name 
1  a,c,b,d,c 
2  d,c,e,a,b

I have tried using listagg with distinct but not able to remove duplicates.

Upvotes: 0

Views: 1024

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

You need a subquery to remove the duplicates, something like;

select id, listagg(name, ',') within group (order by name) as names
from (
  select id, name1 as name from your_table
  union 
  select id, name2 as name from your_table
  union
  select id, name3 as name from your_table
)
group by id

The union will automatically remove duplicates from the combined result set (if you didn't want it to, you'd use union all).

As a demo with a CTE representing your table:

with your_table(id, name1, name2, name3) as (
  select 1,  'a', 'b', 'c' from dual 
  union all select 1, 'c', 'd', 'a' from dual
  union all select 2, 'd', 'e', 'a' from dual
  union all select 2, 'c', 'd', 'b' from dual
)
select id, listagg(name, ',') within group (order by name) as names
from (
  select id, name1 as name from your_table
  union 
  select id, name2 as name from your_table
  union
  select id, name3 as name from your_table
)
group by id;

ID NAMES              
-- --------------------
 1 a,b,c,d             
 2 a,b,c,d,e

You could also have the subquery selecting all three columns and then pivoting them into rows, but with only three this might be simpler.

Upvotes: 1

Related Questions