Nonetheless, it is always possible to write a dynamic SQL (and best yet, inside a plpgsql function) to "automagically handle more fruits..."
Em sáb., 17 de jul. de 2021 às 05:33, Thomas Kellerer <
shammat@gmx.net> escreveu:
Firthouse banu schrieb am 17.07.2021 um 09:24:
> Table1
> Name Fruit
> A Mango
> A Apple
> A Grape
> B. Mango
> B. Mango
> A. Mango
> A. Apple
>
> Output
> Name. Mango Apple Grape
> A. 2. 2. 1
> B. 2. 0. 0
>
> Can anyone write query to get this desired output from table one in Postgres please.
In my opinion, filtered aggregation is the most flexible way to do this kind of thing
select name,
count(*) filter (where fruit = 'Mango') as mango,
count(*) filter (where fruit = 'Apple') as apple,
count(*) filter (where fruit = 'Grape') as grape
from the_table
group by name
order by name;
And before you ask: it's not really possible to make this dynamic, so that the number
of columns "automagically" increases when you have more fruits. One fundamental
restriction of the SQL language is that the number and types of all columns
must be known to the database when the query is parsed. It can't change
the columns as a result of the query itself.