Kata 3 kyu

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

Link

Reference

--

--

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