Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- original example with 2 rows of 3 columns:
CREATE  TABLE tbl (column1 int, column2 text, column3 int);
INSERT INTO tbl VALUES
  (1, 'value1', 3)
, (2, 'value2', 4);
2 rows affected

-- LATERAL VALUES
WITH cte AS (
   SELECT nu.*
   FROM   tbl t
   , LATERAL (
      VALUES
        (1, t.column1::text)
      , (2, t.column2)
      , (3, t.column3::text)
      ) nu(rn, c)
   )
SELECT *
FROM   (TABLE cte OFFSET 0 LIMIT 3) c1
JOIN   (TABLE cte OFFSET 3 LIMIT 3) c2 USING (rn);


rn
c
c


1
1
2

2
value1
value2

3
3
4


-- same with useful column names
WITH cte AS (
   SELECT nu.*
   FROM   tbl t
   , LATERAL (
      VALUES
        ('column1', t.column1::text)
      , ('column2', t.column2)
      , ('column3', t.column3::text)
      ) nu(rn, c)
   )
SELECT * FROM (
   SELECT *
   FROM   (TABLE cte OFFSET 0 LIMIT 3) c1
   JOIN   (TABLE cte OFFSET 3 LIMIT 3) c2 USING (rn)
   ) t (key, row1, row2);


key
row1
row2


column1
1
2

column2
value1
value2

column3
3
4


-- hsotore
SELECT key
     , arr[1] AS row1
     , arr[2] AS row2
FROM  (
   SELECT x.key, array_agg(x.value) AS arr  -- ORDER BY t.ctid
   FROM   tbl t, each(hstore(t)) x
   GROUP  BY 1
   ) sub
ORDER  BY 1;


key
row1
row2


column1
1
2

column2
value1
value2

column3
3
4