CODEWITHSUNDEEP
sqlarrayspostgresqlselectunnest
aniani2020
aniani2020

Reputation: 143

Transposing 2D Arrays using SQL

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}}

Upvotes: 2

Views: 588

Answers (1)

GMB
GMB

Reputation: 222582

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}}

Upvotes: 1

Related Questions