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

Link

Reference