concatenate data in postgresql

Question

I have a table data like this:

I want to concatenate the data as below in postgresql:

I used query as below but there is a small problem:

select 
    Item,
    array_to_string(array_agg(Component) ,', ') AS Component,
    array_to_string(array_agg(Seller) ,', ') AS Seller
from tablename
group by Item

Its giving me below output:

For item A2, its showing C89 two times, I want to show only one time.

Please help me out!!

Answer 1

Use distinct, you also don't need array_agg():

select item, 
       string_agg(distinct component, ',') as component,
       string_agg(distinct seller, ',') as seller
from tablename
group by item;

Answer 2

This should work, sometimes you need to tell it how to order the distinct

select 
    Item,
    array_to_string(array_agg(DISTINCT Component ORDER BY Item DESC ) ,', ') AS Component,
    array_to_string(array_agg((DISTINCT Seller ORDER BY Item DESC) ,', ') AS Seller
from tablename
group by Item