Transpose Column Headers to Rows in Postgresql

transpose column headers to rows in postgresql

Crosstab only does the reverse of what you need, but this should help you:

First create the unnest() function that is included in 8.4, see here for instructions.

Then you can do this (based on this post):

SELECT
   unnest(array['value1Count', 'value2Count', 'value3Count']) AS "Values",
   unnest(array[value1Count, value2Count, value3Count]) AS "Count"
FROM view_name
ORDER BY "Values"

I can verify that this works in 8.4, but because I don't have 8.1, I can't promise it will work the same.

PostgreSQL convert columns to rows? Transpose?

Basing my answer on a table of the form:

CREATE TABLE tbl (
  sl_no int
, username text
, designation text
, salary int
);

Each row results in a new column to return. With a dynamic return type like this, it's hardly possible to make this completely dynamic with a single call to the database. Demonstrating solutions with two steps:

Generate query
Execute generated query

Generally, this is limited by the maximum number of columns a table can hold. So not an option for tables with more than 1600 rows (or fewer). Details:

What is the maximum number of columns in a PostgreSQL select query

Postgres 9.4+

Dynamic solution with `crosstab()`

Use the first one you can. Beats the rest.

SELECT 'SELECT *
FROM   crosstab(
       $ct$SELECT u.attnum, t.rn, u.val
        FROM  (SELECT row_number() OVER () AS rn, * FROM '
                              || attrelid::regclass || ') t
             , unnest(ARRAY[' || string_agg(quote_ident(attname)
                              || '::text', ',') || '])
                 WITH ORDINALITY u(val, attnum)
        ORDER  BY 1, 2$ct$
   ) t (attnum bigint, '
     || (SELECT string_agg('r'|| rn ||' text', ', ')
         FROM  (SELECT row_number() OVER () AS rn FROM tbl) t)
     || ')' AS sql
FROM   pg_attribute
WHERE  attrelid = 'tbl'::regclass
AND    attnum > 0
AND    NOT attisdropped
GROUP  BY attrelid;

Operating with attnum instead of actual column names. Simpler and faster. Join the result to pg_attribute once more or integrate column names like in the pg 9.3 example.

Generates a query of the form:

SELECT *
FROM   crosstab(
   $ct$
   SELECT u.attnum, t.rn, u.val
   FROM  (SELECT row_number() OVER () AS rn, * FROM tbl) t
       , unnest(ARRAY[sl_no::text,username::text,designation::text,salary::text]) WITH ORDINALITY u(val, attnum)
   ORDER  BY 1, 2$ct$
   ) t (attnum bigint, r1 text, r2 text, r3 text, r4 text);

This uses a whole range of advanced features. Just too much to explain.

Simple solution with `unnest()`

One unnest() can now take multiple arrays to unnest in parallel.

SELECT 'SELECT * FROM unnest(
  ''{sl_no, username, designation, salary}''::text[]
, ' || string_agg(quote_literal(ARRAY[sl_no::text, username::text, designation::text, salary::text])
              || '::text[]', E'\n, ')
    || E') \n AS t(col,' || string_agg('row' || sl_no, ',') || ')' AS sql
FROM   tbl;

Result:

SELECT * FROM unnest(
 '{sl_no, username, designation, salary}'::text[]
,'{10,Joe,Music,1234}'::text[]
,'{11,Bob,Movie,2345}'::text[]
,'{12,Dave,Theatre,2356}'::text[])
 AS t(col,row1,row2,row3,row4);

db<>fiddle here

_{Old sqlfiddle}

Postgres 9.3 or older

Dynamic solution with `crosstab()`

Completely dynamic, works for any table. Provide the table name in two places:

SELECT 'SELECT *
FROM   crosstab(
       ''SELECT unnest(''' || quote_literal(array_agg(attname))
                           || '''::text[]) AS col
             , row_number() OVER ()
             , unnest(ARRAY[' || string_agg(quote_ident(attname)
                              || '::text', ',') || ']) AS val
        FROM   ' || attrelid::regclass || '
        ORDER  BY generate_series(1,' || count(*) || '), 2''
   ) t (col text, '
     || (SELECT string_agg('r'|| rn ||' text', ',')
         FROM (SELECT row_number() OVER () AS rn FROM tbl) t)
     || ')' AS sql
FROM   pg_attribute
WHERE  attrelid = 'tbl'::regclass
AND    attnum > 0
AND    NOT attisdropped
GROUP  BY attrelid;

Could be wrapped into a function with a single parameter ...

Generates a query of the form:

SELECT *
FROM   crosstab(
       'SELECT unnest(''{sl_no,username,designation,salary}''::text[]) AS col
             , row_number() OVER ()
             , unnest(ARRAY[sl_no::text,username::text,designation::text,salary::text]) AS val
        FROM   tbl
        ORDER  BY generate_series(1,4), 2'
   ) t (col text, r1 text,r2 text,r3 text,r4 text);

Produces the desired result:

col         r1    r2      r3     r4
-----------------------------------
sl_no       1      2      3      4
username    A      B      C      D
designation XYZ    RTS    QWE    HGD
salary      10000  50000  20000  34343

Simple solution with `unnest()`

SELECT 'SELECT unnest(''{sl_no, username, designation, salary}''::text[] AS col)
     , ' || string_agg('unnest('
                    || quote_literal(ARRAY[sl_no::text, username::text, designation::text, salary::text])
                    || '::text[]) AS row' || sl_no, E'\n     , ') AS sql
FROM   tbl;

Slow for tables with more than a couple of columns.

Generates a query of the form:

SELECT unnest('{sl_no, username, designation, salary}'::text[]) AS col
     , unnest('{10,Joe,Music,1234}'::text[]) AS row1
     , unnest('{11,Bob,Movie,2345}'::text[]) AS row2
     , unnest('{12,Dave,Theatre,2356}'::text[]) AS row3
     , unnest('{4,D,HGD,34343}'::text[]) AS row4

Same result.

How to transpose column to row and duplicate the original column header?

Use a lateral join:

select v.*
from t cross join lateral
     (values ('a', a), ('b', b), ('c', c), ('d', d), ('e', e), ('f', f)
     ) v(col, val);

Compared to the union all approach, this only scans the table once. This can be a bit performance win if your "table" is really a more complex query.

SQL Transpose Rows as Columns

Use:

  SELECT r.user_id,
         MAX(CASE WHEN r.question_id = 1 THEN r.body ELSE NULL END) AS "Do you like apples?",
         MAX(CASE WHEN r.question_id = 2 THEN r.body ELSE NULL END) AS "Do you like oranges?",
         MAX(CASE WHEN r.question_id = 3 THEN r.body ELSE NULL END) AS "Do you like carrots?"
    FROM RESPONSES r
    JOIN QUESTIONS q ON q.id = r.question_id
GROUP BY r.user_id

This is a standard pivot query, because you are "pivoting" the data from rows to columnar data.

How to pivoting column to row, also turn column header into row value in SQL?

One method is union all:

SELECT magnitude, 2000 as year, year_2000 as n
FROM tutorial.worldwide_earthquakes
UNION ALL
SELECT magnitude, 2001 as year, year_2001 as n
FROM tutorial.worldwide_earthquakes
UNION ALL
. . .

The query ends with a SELECT, not a UNION ALL.

But in Postgres, I would recommend a lateral join:

SELECT we.magnitude, v.year, v.n
FROM tutorial.worldwide_earthquakes we CROSS JOIN LATERAL
     (VALUES (2000, year_2000),
             (2001, year_2001),
             . . .
     ) v(year, n);

The . . . continues the pattern (and the last does not end in a comma).

Transpose data in postgres table

We went with:

SELECT id,
       unnest(ARRAY['name','field1','field2']) AS field_name,
       unnest(ARRAY["name", "field1", "field2"]) AS new_value
       FROM table

Transpose latest rows per user to columns

Use crosstab() from the tablefunc module.

SELECT * FROM crosstab(
   $$SELECT user_id, user_name, rn, email_address
     FROM  (
        SELECT u.user_id, u.user_name, e.email_address
             , row_number() OVER (PARTITION BY u.user_id
                            ORDER BY e.creation_date DESC NULLS LAST) AS rn
        FROM   usr u
        LEFT   JOIN email_tbl e USING (user_id)
        ) sub
     WHERE  rn < 4
     ORDER  BY user_id
   $$
  , 'VALUES (1),(2),(3)'
   ) AS t (user_id int, user_name text, email1 text, email2 text, email3 text);

I used dollar-quoting for the first parameter, which has no special meaning. It's just convenient to escape single quotes in the query string, which is a common case:

Insert text with single quotes in PostgreSQL

Detailed explanation and instructions:

PostgreSQL Crosstab Query

And in particular, for "extra columns":

Pivot on Multiple Columns using Tablefunc

The special difficulties here are:

The lack of key names.

→ We substitute with row_number() in a subquery.
The varying number of emails.

→ We limit to a max. of three in the outer SELECT and use crosstab() with two parameters, providing a list of possible keys.

Pay attention to NULLS LAST in the ORDER BY.

Transpose Column Headers to Rows in Postgresql