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
;

Link

PLSQL Documentation about Generate_Subscripts

StackOverflow

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Isabelle

Isabelle

In love with telling stories with data