PostgreSQL convert columns to rows? Transpose?

Question

I have a PostgreSQL function (or table) which gives me the following output:

Sl.no    username    Designation    salary   etc..  1        A           XYZ            10000    ...  2        B           RTS            50000    ...  3        C           QWE            20000    ...  4        D           HGD            34343    ... 

Now I want the Output as below:

Sl.no            1       2        3       4       ...  Username        A       B        C       D       ...  Designation     XYZ     RTS      QWE     HGD     ...  Salary          10000   50000    20000   34343   ... 

How to do this?

Answer 1

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:

1. Generate query
2. 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); 

SQL Fiddle running on pg 9.6

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.

Answer 2

SELECT    unnest(array['Sl.no', 'username', 'Designation','salary']) AS "Columns",    unnest(array[Sl.no, username, value3Count,salary]) AS "Values" FROM view_name ORDER BY "Columns" 

Reference : convertingColumnsToRows