Обсуждение: Need query
Table1
Name FruitA 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.
TIA
On Saturday, July 17, 2021, Firthouse banu <penguinsfairy@gmail.com> wrote:
Can anyone write query to get this desired output from table one in Postgres please.
No, but I’ll point you in a useful direction.
David J.
> Can anyone write query to get this desired output from table one in Postgres please.
some links ; maybe useful :
keywords: crosstab, tablefunc,
Best,
Imre
Firthouse banu <penguinsfairy@gmail.com> ezt írta (időpont: 2021. júl. 17., Szo, 9:25):
Table1Name FruitA MangoA AppleA GrapeB. MangoB. MangoA. MangoA. AppleOutputName. Mango Apple GrapeA. 2. 2. 1B. 2. 0. 0Can anyone write query to get this desired output from table one in Postgres please.TIA
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.
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.
Caetano Traina Junior schrieb am 18.07.2021 um 15:19:> >> Em sáb., 17 de jul. de 2021 às 05:33, Thomas Kellerer <shammat@gmx.net <mailto:shammat@gmx.net>> escreveu: >> 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. > Nonetheless, it is always possible to write a dynamic SQL (and best > yet, inside a plpgsql function) to "automagically handle more > fruits..." Even if you create a function that uses dynamic SQL, you still have to specify each output column "manually" when calling the function. So that doesn't really buy you anything.