PostgreSQL: Aggregate rows together based on the same id-CodePudding

Imagine the data:

id  name  2019           2020        2021
1   Ana   {fruit,health}      
1   Ana                             {veggie}
2   Bill  {beauty}
2   Bill                 {veggie}
2   Bill                            {health,veggie}

I'm aiming for the result:

id  name  2019            2020        2021
1   Ana   {fruit, health}           {veggie}
2   Bill  {beauty}        {veggie}    {health,veggie}

How could I achieve these result? I'm struggling to find cases like this.

CodePudding user response：

with imagine_the_data(id,name,"2019","2020","2021") AS
(
   select 1,   'Ana', '{fruit,health}',null,null union all 
   select 1,'Ana',null,null,'{veggie}' union all 
   select 2,'Bill','{beauty}' ,null,null union all  
   select 2,'Bill',null,'{veggie}' ,null union all 
   select 2,'Bill' ,null,null,' {health,veggie}'
)
 select max(i.id)as id,i.name,max(i."2019")as max_2019,max(i."2020")as max_2020,
        max(i."2021")as max_2021
 from imagine_the_data i
 group by i.name

May be this one is suitable