## Codewars SQL

Kata 3 kyu

## 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) 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) 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;

Other Solution:

`SELECT`

ARRAY(

SELECT (

ARRAY (

SELECT matrix[j][s]

FROM ( SELECT generate_subscripts(matrix, 1) AS j ) foo

)

)

FROM ( SELECT generate_subscripts(matrix, 2) AS s ) bar

) as matrix

FROM public.matrices m