CODEWITHSUNDEEP
arrayspostgresql
bublitz
bublitz

Reputation: 920

PostgreSQL: Merge Columns and rows into array

I have currently a problem, where I want to merge multiple columns into one building an array. Furthermore, I want to group by another column to build add a second dimension to the array.

Here is the current situation:

function            X        Y       Z
A                   1        4       {7,9,...}
A                   2        6       {8,3,...}
A                   4        4       {6,1,...}
A                   3        2       {1,0,...}
B                   1        2       {1,1,...}

After merging columns X, Y and Z into one column:

function            XYZ
A                   {1,4,7,9,...}
A                   {2,6,8,3,...}
A                   {4,4,6,1,...}
A                   {3,2,1,0,...}
B                   {1,2,1,1,...}

I want to group by column function and build a 2 dimensional array as a result. The entries within the array should be ordered by column X.

function            XYZ
A                   {{1,4,7,9,...},{2,6,8,3,...},{3,2,1,0,...},{4,4,6,1,...}}                
B                   {{1,2,1,1,...}}

I know, that I can use array_agg to aggregate over one column to put entries into the array, but I did not get this case to work.

Upvotes: 1

Views: 725

Answers (1)

cske
cske

Reputation: 2243

If I understand correctly

CREATE TABLE f (fn text,x int,y int,z int[] );
INSERT INTO f(fn, x, y, z) values ('A',1,4,ARRAY[7,8,9]);
INSERT INTO f(fn, x, y, z) values ('A',2,6,ARRAY[7,8,9]);
INSERT INTO f(fn, x, y, z) values ('A',4,4,ARRAY[7,8,9]);
INSERT INTO f(fn, x, y, z) values ('B',1,4,ARRAY[7,8,9]);
INSERT INTO f(fn, x, y, z) values ('A',9,4,ARRAY[7,8,9]);
INSERT INTO f(fn, x, y, z) values ('A',5,4,ARRAY[7,8,9]);

SELECT fn,ARRAY_AGG(v) FROM 
(SELECT fn,ARRAY[x]||ARRAY[y]||z as v from f ORDER BY x) x GROUP BY fn;

Upvotes: 2

Related Questions