postgresql - distinct only on one column

Question

I have a table called eatable. It has columns such as type, name.

type column has fruit, veggie, veggie, fruit, veggie values .

name column has apple, brinjal, carrot, banana, cabbage.values

I want the output as, type column must display only 2 rows and name should display all the values.

It should like below:

I have tried the below query, but it isn't I am expected:

select distinct on (type) type, name from eatable;

Help me out!!

Answer 1

You can use PostgreSQL's Aggregate Functions

SELECT type
    ,string_agg(NAME, ',') "name"
FROM eatable
GROUP BY type;

Result:

type   name  
text   text                 
------ ---------------------- 
fruit  apple,banana           
veggie brinjal,carrot,cabbage 

OR

SELECT type
    ,array_agg(name) "name"
FROM eatable
GROUP BY type;

Result:

type   name  
text   text[]                   
------ ------------------------ 
fruit  {apple,banana}           
veggie {brinjal,carrot,cabbage} 

---

Demo