Transposing 2D Arrays using SQL

Question

Consider this table definition in SQL

CREATE TABLE matrices (
matrix text[][] NOT NULL
);

Assuming that every matrix in this table has the same dimensions, how to write a query to transpose them?

for example:

INSERT INTO matrices VALUES
(array[['1','2','3'],
       ['4','5','6']]),
(array[['f','e','d'],
       ['c','b','a']]);

after the transpose it should look like:

 {{1,4},{2,5},{3,6}}
 {{f,c},{e,b},{d,a}}

Unnest the array until separate elements storing their positional indices for each dimention then aggregate back in needed order. — Akina
– Akina, Commented Jun 2, 2020 at 9:25

GMB · Accepted Answer · 2020-06-13 00:31:42Z

1

An option uses generate_subscripts() to enumerate the indexes of each dimension of the array, and then two levels of aggregation.

We need a primary key column to properly rebuild each array - I assumed id (if you don't have such a column, we could generate it on the fly in a subquery with row_number()):

select id, array_agg(ar order by j) new_matrix
from (
    select m.id, j, array_agg(matrix[i][j] order by i) ar
    from matrices m
    cross join lateral generate_subscripts(m.matrix, 1) as s1(i)
    cross join lateral generate_subscripts(m.matrix, 2) as s2(j)
    group by m.id, j
) t
group by id

Demo on DB Fiddle

Sample data:

id | matrix           
-: | :----------------
 1 | {{1,2,3},{4,5,6}}
 2 | {{f,e,d},{c,b,a}}

Results:

id | new_matrix         
-: | :------------------
 1 | {{1,4},{2,5},{3,6}}
 2 | {{f,c},{e,b},{d,a}}

answered Jun 13, 2020 at 0:31

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Transposing 2D Arrays using SQL

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