postgreSQL getting column values as new column and adding

Question

I have the following table

ident	name	count
A1	X	1
A1	Y	2
A1	X	6
A2	X	2
A2	Z	3

What i need is a new table which should look like:

ident	X	Y	Z
A1	7	2	0
A2	2	0	3

so it should give me for every distinct id a sum of all the existing names. But the columns with are build out of the names should be build automaticly.

i have try

SELECT
  ident ,
  MAX(CASE WHEN (name = 'X') THEN 1 ELSE NULL END) AS X
FROM
  mytable
GROUP BY ident 
ORDER BY ident 

but with this code i have to make the columns and just can set the sum to 0 or 1.

Thx

Answer 1

Use conditional aggregation, which in Postgres looks like this:

select ident,
       sum(count) filter (where name = 'X') as x_sum,
       sum(count) filter (where name = 'Y') as y_sum,
       sum(count) filter (where name = 'Z') as z_sum
from mytable
group by ident;

The FILTER clause is Standard SQL, but Postgres is one of the few databases that actually supports it. You can, of course, do the same thing with CASE expressions, but FILTER gives the optimizer more hints that can help performance (and many people find it cleaner).