PostgreSQL convert columns to rows? Transpose?

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?

Answers


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:

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.

Postgres 9.4+
Dynamic solution with crosstab()

Use this if 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.3.


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


If (like me) you were needing this information from a bash script, note there is a simple command-line switch for psql to tell it to output table columns as rows:

psql mydbname -x -A -F= -c "SELECT * FROM foo WHERE id=123"

The -x option is the key to getting psql to output columns as rows.


I have a simpler approach than Erwin pointed above, that worker for me with Postgres (and I think that it should work with all major relational databases whose support SQL standard)

You can use simply UNION instead of crosstab:

SELECT text 'a' AS "text" UNION SELECT 'b';

 text
------
 a
 b
(2 rows)

Of course that depends on the case in which you are going to apply this. Considering that you know beforehand what fields you need, you can take this approach even for querying different tables. I.e.:

SELECT 'My first metric' as name, count(*) as total from first_table UNION
SELECT 'My second metric' as name, count(*) as total from second_table 

 name             | Total
------------------|--------
 My first metric  |     10
 My second metric |     20
(2 rows)

It's a more maintainable approach, IMHO. Look at this page for more information: https://www.postgresql.org/docs/current/typeconv-union-case.html


There is no proper way to do this in plain SQL or PL/pgSQL.

It will be way better to do this in the application, that gets the data from the DB.


Need Your Help

Can an Objective-C protocol have category?

objective-c objective-c-category objective-c-protocol

Objective-C category can implement a protocol. Is the reverse true - can an Objective-C protocol have category?

Wordpress Media Library not showing images

php wordpress image-uploading

Hello I have a wordpress site and I upload images to media library but the images do not display. In wp-content/upload folder there are other folders like 2015/ and then in that folder there are fo...