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;
Demo
Related Topics
Percent to Total in Postgresql Without Subquery
Mysql Count() to Return 0 If No Records Found
How to Get Depatment Wise Max Salary as Well as Name of Employee Having It
How to Extract Year from Hire_Date in My SQL
Selecting First and Last Values in a Group
How to Split a Record Based on Dates in SQL Server
How to Make an If Statement in Ssis
Sqlstate[Hy000] [1045] Access Denied for User 'Root'@'Localhost' (Using Password: No)
Oracle SQL Get the First and Last Records from an Ordered Dataset
Group MySQL Query by 15 Min Intervals
How to Get the Month and Day With Leading 0'S in SQL (E.G. 9 => 09)
How to Change Column Order in a Table Using SQL Query in SQL Server 2005
How to Parser a SQL Query to Pull Out the Column Names and Table Names
SQL Select Only Rows with Max Value on a Column
Sqlcmd Not Able to Find a Library (Libmsodbcsql-17.0.So.1.1) That Is There
Converting to Timestamp With Time Zone Failed on Athena