Subject: Re: Need query
To: pgsql-admin@lists.postgresql.org
References: 
 <CAK_Ha4xccRpDqzC-M5NWXn1+saRXbC_PVn3Rw0Lo=qBHxQDhuw@mail.gmail.com>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <d3309e65-ddff-439e-49a1-7a42a0479001@gmx.net>
Date: Sat, 17 Jul 2021 10:32:45 +0200
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.21)
 Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
MIME-Version: 1.0
In-Reply-To: 
 <CAK_Ha4xccRpDqzC-M5NWXn1+saRXbC_PVn3Rw0Lo=qBHxQDhuw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: de-DE
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/d3309e65-ddff-439e-49a1-7a42a0479001%40gmx.net>
Precedence: bulk

Firthouse banu schrieb am 17.07.2021 um 09:24:
> Table1
> Name =C2=A0 =C2=A0 =C2=A0Fruit
> A =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Mango
> A =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Apple
> A =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Grape
> B. =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Mango
> B. =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Mango
> A. =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Mango
> A. =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Apple
>
> Output
> Name. =C2=A0 Mango =C2=A0 Apple =C2=A0 Grape
> A. =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 2. =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A02. =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01
> B. =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 2. =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 0. =C2=A0 =C2=A0 =C2=A0 =C2=A0 0
>
> Can anyone write query to get this desired output from table one in Post=
gres please.

In my opinion, filtered aggregation is the most flexible way to do this ki=
nd of thing

select name,
        count(*) filter (where fruit =3D 'Mango') as mango,
        count(*) filter (where fruit =3D 'Apple') as apple,
        count(*) filter (where fruit =3D '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 fundam=
ental
restriction of the SQL language is that the number and types of all column=
s
must be known to the database when the query is parsed. It can't change
the columns as a result of the query itself.