ARRAY_AGG() grouped by another column's values

Question

Here's my table like below

p_no  type  name  value
------------------------
1     A     Tomy  1
1     A     Nick  2
1     B     Tomy  3
1     B     Nick  4
1     C     Tomy  5
1     C     Nick  4
2     A     Tomy  8
2     A     Nick  7
2     B     Tomy  5
2     B     Nick  4

It means each p_no represents a block that records poeple's value in both A, B, C type (might be more types).

I want to select a person with his value of all types

Given p_no=1, name=Tomy

A    B    C    D
------------------
1    3    5    ..

I have tried

SELECT p_no, t[1] A, t[2] B/*, t[3] C.....*/
FROM (
    SELECT p_no, ARRAY_AGG(type) FILTER (WHERE name='Tomy') t
    FROM type
    GROUP BY 1
    ORDER BY 1) _unused

but it cannnot make the ARRAY_AGG() include the value

I think I need something similar to ARRAY_AGG('value' GROUP BY 'type')

Is this kind of query possible or is there any better alternatives?

Answer 1

Your intuition is right. You want to aggregate value ordered by type, so:

select p_no, t[1] as "A", t[2] as "B", t[3] as "C"
from (
    select p_no, array_agg(value order by type) as t
    from my_table
    where name = 'Tomy' 
    group by p_no
    ) s

db<>fiddle.