Query distinct on multiple columns and aggregate others in Postgres

Question

I have a table like this:

X,Y,Z,A,B,C
X,Y,Z,D,E,F
X,Y,Z,G,H,I
X,Y,W,A,C,D
X,Y,W,H,I,J

Obviously it is non-normalized table. I want a query that returns the following result:

X,Y,Z,{{A,D,G},{B,E,H},{C,F,I}}
X,Y,W,{{A,H},{C,I},{D,J}}

Normalization is very expensive.Any ideas?

Hernán Daniel Garau · Accepted Answer · 2016-04-07 01:16:43Z

1

try this:

SELECT c1,c2,c3,ARRAY[array_agg(c4),array_agg(c5)]
from your_table
group by c1,c2,c3

answered Apr 7, 2016 at 1:16

1361 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1