Postgresql Convert Columns to Rows Transpose

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.

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.

Convert columns data into rows in PostgreSQL

You can create a "temporary" normalized view on the data using a cross join:

select o.order_no, o.rate, v.*
from orders o
  cross join lateral (
      values 
        ('jan', jan), 
        ('feb', feb), 
        ('mar', mar), 
        ... 
        ('dec', dec)
  ) as v(month, unit)

If you want to exclude the months with no values, you can add

where v.unit is not null

to the query

Online example: http://rextester.com/PBP46544

PostgreSQL and columns to rows

use arrays and unnest

select city, 
       year, 
       sex,  
       unnest(array[age_0 , age_1 , age_2 , ..., age_115]) as amount,
       unnest(array[ 0 , 1 , 2 , ... ,  115]) as age 
from mytable

on large datasets this might be slow

did a quick look, there are many similar questions already asked , one good one with a good guide to dynamically generate the query you need ... les pasting for you link

generate query idiea

SELECT 'SELECT city , year , sex ,  unnest(ARRAY[' || string_agg(quote_ident(attname) , ',') || ']) AS amount  from mytable' AS sql
FROM   pg_attribute  
WHERE  attrelid = 'mytable'::regclass  and attname ~ 'age_'
AND    attnum > 0
AND    NOT attisdropped
GROUP  BY attrelid;

Postgres Transpose Rows to Columns Based on Column Value

Assuming you have a fixed 4 quarters per year which you want to display, use pivoting logic:

SELECT
    stock,
    year,
    MAX(amount) FILTER (WHERE statement = 'Q1 Earnings') AS "Q1 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q2 Earnings') AS "Q2 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q3 Earnings') AS "Q3 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q4 Earnings') AS "Q4 Earnings"
FROM statement
GROUP BY
    stock,
    year;

How to transpose columns and rows in PostgreSQL (i.e., how do I switch rows and columns)?

You can do this easily with an aggregate function and a CASE statement:

select year,
  sum(case when place = 'U.S.' then price else 0 end) "U.S.",
  sum(case when place = 'U.K.' then price else 0 end) "U.K."
from yourtable
group by year

See SQL Fiddle with Demo

Dynamic transpose for unknown row value into column name on postgres

You can't have a "dynamic" pivot as the number, names and data types of all columns of a query must be known to the database before the query is actually executed (i.e. at parse time).

I find aggregating stuff into a JSON easier to deal with.

select customer_number,
       jsonb_object_agg(label, value) as props
from the_table
group by customer_number

If your frontend can deal with JSON values directly, you can stop here.

If you really need a view with one column per attribute, you can them from the JSON value:

select customer_number, 
       props ->> 'address' as address,
       props ->> 'phone' as phone,
       props ->> 'email' as email
from (       
  select customer_number,
         jsonb_object_agg(label, value) as props
  from the_table
  group by customer_number
) t

I find this a bit easier to manage when new attributes are added.

If you need a view with all labels, you can create a stored procedure to dynamically create it. If the number of different labels doesn't change too often, this might be a solution:

create procedure create_customer_view() 
as
$$
declare
  l_sql text;
  l_columns text;
begin
  select string_agg(distinct format('(props ->> %L) as %I', label, label), ', ')
    into l_columns
  from the_table;
  
  l_sql := 
    'create view customer_properties as 
     select customer_number, '||l_columns||' 
     from (
      select customer_number, jsonb_object_agg(label, value) as props
       from the_table 
       group by customer_number 
     ) t';
  execute l_sql;
end;
$$
language plpgsql;

Then create the view using:

call create_customer_view();

And in your code just use:

select *
from customer_properties;

You can schedule that procedure to run in regular intervals (e.g. through a cron job on Linux)

PostgreSQL transpose rows in to columns

you can try this:

  select sales_report.date, 
  sum(case when id=1 then quantity else 0 end) as '1',
  sum(case when id=2 then quantity else 0 end) as '2',
  sum(case when id=3 then quantity else 0 end) as '3',
  sum(case when id=4 then quantity else 0 end) as '4',
  sum(case when id=5 then quantity else 0 end) as '5',
  sum(case when id=6 then quantity else 0 end) as '6',
  sum(case when id=7 then quantity else 0 end) as '7',
  sum(case when id=8 then quantity else 0 end) as '8',
  sum(case when id=9 then quantity else 0 end) as '9',
  sum(case when id=10 then quantity else 0 end) as '10',
  sum(case when id=11 then quantity else 0 end) as '11',
  sum(case when id=12 then quantity else 0 end) as '12',
  sum(case when id=13 then quantity else 0 end) as '13'
  from sales_report where date = '2018-10-04' GROUP BY Date ORDER BY date ASC

PostgreSQL simply transpose/convert rows to columns

You could use a pivot query with the help of ROW_NUMBER:

WITH cte AS (
    SELECT value, ROW_NUMBER() OVER (ORDER BY value) rn
    FROM yourTable
)

SELECT
    MAX(value) FILTER (WHERE rn = 1) AS tmp,
    MAX(value) FILTER (WHERE rn = 2) AS tmp02,
    MAX(value) FILTER (WHERE rn = 3) AS tmp03
FROM cte;

screen capture from demo link below

Postgresql Convert Columns to Rows Transpose