Using PIVOT in conjunction with a total summation in SQL

I have some data with a general structure:

DATE   CATEGORY   VALUE
------------------------------
A      X          X'
A      Y          X'
A      Z          Z'

I want to output a single row like:

DATE VALUE_X VALUE_Y VALUE_Z TOTAL
------------------------------------------
A    X'      Y'      Z'      X'+Y'+Z'

I can see that the way to approach this is with a PIVOT, but how would I do this whilst also calculating the total?

Thank

Answer

You can use conditional aggregation:

select date,
       max(value) filter (where category = 'X') as x,
       max(value) filter (where category = 'Y') as y,
       max(value) filter (where category = 'Z') as z,
       sum(value)
from t
group by date;

Note: If you have values other than 'X', 'Y', and 'Z', then you might want either:

sum(value) filter (where category in ('X', 'Y', 'Z')

or:

where where category in ('X', 'Y', 'Z'

I am also assuming that the last column is actually for the sum. If you want a string aggregation, then the logic is:

       string_agg(value, '+' order by category)