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.
