ARRAY_AGG() grouped by another column's values-CodePudding

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?

CodePudding user response：

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.