PostgreSQL: Merge Columns and rows into array

Question

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.

cske · Accepted Answer · 2016-11-21 07:48:46Z

2

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;

answered Nov 21, 2016 at 7:48

cske

2,2334 gold badges27 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: Merge Columns and rows into array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related