How to unpivot a table in PostgreSQL
A completely dynamic version requires dynamic SQL. Use a plpgsql function with EXECUTE
:
For Postgres 9.2 or older (before LATERAL
was implemented):
CREATE OR REPLACE FUNCTION f_unpivot_years92(_tbl regclass, VARIADIC _years int[])
RETURNS TABLE(id int, year int, value int) AS
$func$
BEGIN
RETURN QUERY EXECUTE '
SELECT id
, unnest($1) AS year
, unnest(ARRAY["'|| array_to_string(_years, '","') || '"]) AS val
FROM ' || _tbl || '
ORDER BY 1, 2'
USING _years;
END
$func$ LANGUAGE plpgsql;
For Postgres 9.3 or later (with LATERAL
):
CREATE OR REPLACE FUNCTION f_unpivot_years(_tbl regclass, VARIADIC _years int[])
RETURNS TABLE(id int, year int, value int) AS
$func$
BEGIN
RETURN QUERY EXECUTE (SELECT
'SELECT t.id, u.year, u.val
FROM ' || _tbl || ' t
LEFT JOIN LATERAL (
VALUES ' || string_agg(format('(%s, t.%I)', y, y), ', ')
|| ') u(year, val) ON true
ORDER BY 1, 2'
FROM unnest(_years) y
);
END
$func$ LANGUAGE plpgsql;
About VARIADIC
:
- Return rows matching elements of input array in plpgsql function
Call for arbitrary years:
SELECT * FROM f_unpivot_years('tbl', 1961, 1964, 1963);
Same, passing an actual array:
SELECT * FROM f_unpivot_years('tbl', VARIADIC '{1960,1961,1962,1963}'::int[]);
For a long list of sequential years:
SELECT *
FROM f_unpivot_years('t', VARIADIC ARRAY(SELECT generate_series(1950,2014)));
For a long list with regular intervals (example for every 5 years):
SELECT *
FROM f_unpivot_years('t', VARIADIC ARRAY(SELECT generate_series(1950,2010,5)));
Output as requested.
The function takes:
1. A valid table name - double-quoted if it's otherwise illegal (like '"CaMeL"'
). Using the object identifier type regclass
to assert correctness and defend against SQL injection. You may want to schema-qualify the tale name to be unambiguous (like 'public."CaMeL"'
). More:
- Table name as a PostgreSQL function parameter
2. Any list of numbers corresponding to (double-quoted) column names.
Or an actual array, prefixed with the keyword VARIADIC
.
The array of columns does not have to be sorted in any way, but table and columns must exist or an exception is raised.
Output is sorted by id
and year
(as integer
). If you want years to be sorted according to the sort order of the input array, make it just ORDER BY 1
. Sort order according to array is not strictly guaranteed, but works in the current implementation. More about that:
- PostgreSQL unnest() with element number
Also works for NULL
values.
SQL Fiddle for both with examples.
References:
Is there something like a zip() function in PostgreSQL that combines two arrays?
Table name as a PostgreSQL function parameter
PostgreSQL generate_series() with SQL function as arguments
Postgres: convert single row to multiple rows (unpivot)
A single SELECT
with a LATERAL
join to a VALUES
expression does the job:
SELECT p.id, v.*
FROM price_list p
, LATERAL (
VALUES
('type_a', p.price_type_a)
, ('type_b', p.price_type_b)
, ('type_c', p.price_type_c)
) v (price_type, price);
Related:
- Convert one row into multiple rows with fewer columns
- SELECT DISTINCT on multiple columns
How to split the row by a comma and unpivot the table in PostgreSQL?
you can use string_to_array()
and unnest()
SELECT t.birthday_id ,
t.child_birthday_id,
p.place
FROM sh.test t
CROSS JOIN LATERAL unnest(string_to_array(place, ',')) as p(place);
If you are using Postgres 14 you can also use string_to_table(t.place, ',')
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;
Unpivot postgres table having multiple columns
Since you tagged the question with SparkSQL, here's a solution using stack
function. Applied to your example:
df = spark.createDataFrame([
(1, 8, 9, 10, 11, 12, 13, 14),
(2, 15, 16, 17, 18, 19, 20, 21),
(3, 22, 23, 24, 25, 26, 27, 28)
], ["ID", "week1", "week2", "week3", "week4", "week5", "week6", "week7"])
df.createOrReplaceTempView("my_table")
spark.sql("""
SELECT ID,
stack(7, '1', week1, '2', week2, '3', week3, '4', week4, '5', week5, '6', week6, '7', week7) as (week_number, week_value)
FROM my_table
""").show()
#+---+-----------+----------+
#| ID|week_number|week_value|
#+---+-----------+----------+
#| 1| 1| 8|
#| 1| 2| 9|
#| 1| 3| 10|
#| 1| 4| 11|
#| 1| 5| 12|
#| 1| 6| 13|
#| 1| 7| 14|
#| 2| 1| 15|
#| 2| 2| 16|
#| 2| 3| 17|
#| 2| 4| 18|
#| 2| 5| 19|
#| 2| 6| 20|
#| 2| 7| 21|
#| 3| 1| 22|
#| 3| 2| 23|
#| 3| 3| 24|
#| 3| 4| 25|
#| 3| 5| 26|
#| 3| 6| 27|
#| 3| 7| 28|
#+---+-----------+----------+
Unpivot in postgres with a column created in the same query
Not sure to well understand your issue, maybe a subquery can help :
select s.baseline
from
( select
"Name",
case
when "Year"='2020' then "Date"
end as "Baseline"
from "test_table"
) AS s
Related Topics
Creating a Composite Foreign Key in SQL Server 2008
SQL Use Case Statement in Where in Clause
Find All Rows with Null Value(S) in Any Column
If I Stop a Long Running Query, Does It Rollback
Add Emoji/Emoticon to SQL Server Table
Any Reason for Group by Clause Without Aggregation Function
Intersection of Multiple Arrays in Postgresql
Split Date Range into One Row Per Month in SQL Server
Does Oracle Roll Back the Transaction on an Error
Split a String into Rows Using Pure SQLite
SQL Server 2008 Insert with While Loop
Ssis Connection Not Found in Package
SQL Server After Update Trigger
Is There an Automatic Modification Time Stamp Type for Oracle Columns