How to Unpivot a Table in Postgresql

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



Leave a reply



Submit