# Codewars SQL

## Challenge: Transpose two-dimensional arrays

Given a table with a following schema

`Table "public.matrices"`

Column | Type | Modifiers

--------+--------+-----------

matrix | text[] | not null

which holds a set of two-dimensional text arrays i.e.

`matrix`

-------------------

{{1,2,3},{4,5,6}}

{{a,b,c},{d,e,f}}

(2 rows)

your goal is to wite a `SELECT`

statement or a CTE that returns array data in a transposed form

`matrix`

---------------------

{{1,4},{2,5},{3,6}}

{{a,d},{b,e},{c,f}}

(2 rows)

You can’t use / create user definded functions and resort to procedural PL/pgSQL.

Solution:

select array_agg(v order by j) as matrix

from

( select rn,

j,

array_agg(v order by i) as v

from

( select rn,

i,

j,

matrix[i][j] as v

from

( select generate_subscripts(matrix, 2) as j, q.* from(

select row_number() over() as rn,

generate_subscripts(matrix, 1) as i, matrix

from matrices )q )r )s

group by rn, j

)t

group by rn

order by rn

;