Re: Need query

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(at)gmx(dot)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.
>
>
>