CODEWITHSUNDEEP
sqlarrayspostgresqltranspose
Yanbin Hu
Yanbin Hu

Reputation: 537

How to transpose two-dimension arrays in PostgreSQL

A table

create table matrices(
 matrix text[][] not null);

It's value: 

 insert into Matrices values
    (array[ ['1','2','3'],
            ['4','5','6'] ]),
    (array[ ['a','b','c'],
            ['d','e','f'] ]);

How Could I write SQL to transpose each array, so that the result looks like:

   matrix
-------------
{{1,4},{2,5},{3,6}}
{{a,d},{b,e},{c,d}}
(2 rows)

Upvotes: 3

Views: 3087

Answers (3)

souzsouz
souzsouz

Reputation: 1

this also works

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 matrices m

Upvotes: 0

FunctorSalad
FunctorSalad

Reputation: 2622

Simplifying this a little:

create or replace function array_transpose(arr anyarray)
returns anyarray as
$f$
    select array_agg(
            (select array_agg(arr[i][j] order by i)
                from generate_subscripts(arr, 1) as i)
            order by j
           )
        from generate_subscripts(arr, 2) as j
$f$ 
language sql immutable;

Upvotes: 0

peterm
peterm

Reputation: 92805

A bit verbose but here it is

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;

Here is a dbfiddle demo

Or create a function

CREATE OR REPLACE FUNCTION transpose_2d(anyarray)
RETURNS anyarray AS $$
SELECT array_agg(v ORDER BY j) matrix  FROM (
    SELECT j, array_agg(v ORDER BY i) AS v FROM (
        SELECT i, j, $1[i][j] AS v FROM (
            SELECT generate_subscripts($1, 2) j, q.* FROM (
                SELECT generate_subscripts($1, 1) AS i, $1
            ) q
        ) r
    ) s
     GROUP BY j
) t
$$ LANGUAGE sql IMMUTABLE;

Usage

SELECT transpose_2d(matrix) 
  FROM matrices;

Here is a dbfiddle demo

Output (in both cases):

       matrix
---------------------
 {{1,4},{2,5},{3,6}}
 {{a,d},{b,e},{c,f}}
(2 rows)

Upvotes: 4

Related Questions