Postgres: Convert Single Row to Multiple Rows (Unpivot)

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

Convert a single row into multiple rows by the columns in Postgresql

Use json functions:

select key as denomination, value as quantity
from cash_drawer c,
lateral json_each(row_to_json(c))
where key <> 'date'
and date = '2016-11-25';

 denomination | quantity 
--------------+----------
 100          | 5
 50           | 12
 20           | 27
 10           | 43
 5            | 147
 1            | 129
(6 rows)

Test it here.

postgresql - Change single row to multiple rows

version with whitespace (empty text)
The unnest function can do this for you.
And if you want the empty text then you can use this

SELECT ROW_NUMBER() OVER (ORDER BY paymentid) AS "group",  
unnest(array[1, 2, 3]) AS "line item",  
unnest(array[paymentid, '', '']) AS "paymentid",  
unnest(array[customercode, '', '']) AS "customercode",  
unnest(array['PREVIOUS BALANCE', 'PAYMENT AMOUNT', 'REMAINING BALANCE']) AS "type",  
unnest(array[previousbalance, paymentamount, remainingbalance]) AS "amount"  
FROM payment_info  
ORDER BY 1, 2 ;  

To get this

 group | line item | paymentid | customercode |       type        | amount 
-------+-----------+-----------+--------------+-------------------+--------
     1 |         1 | PID0001   | CUST024      | PREVIOUS BALANCE  |  10000
     1 |         2 |           |              | PAYMENT AMOUNT    |   2500
     1 |         3 |           |              | REMAINING BALANCE |   7500
     2 |         1 | PID0002   | CUST031      | PREVIOUS BALANCE  |   8500
     2 |         2 |           |              | PAYMENT AMOUNT    |   3500
     2 |         3 |           |              | REMAINING BALANCE |   5000
     3 |         1 | PID0003   | CUST005      | PREVIOUS BALANCE  |  12000
     3 |         2 |           |              | PAYMENT AMOUNT    |   1500
     3 |         3 |           |              | REMAINING BALANCE |  10500

If you want to have, for example points or other text, or arrows in the empty text columns, you can do this easily with unnest.

You can control the 4 empty text values individually.

SELECT ROW_NUMBER() OVER (ORDER BY paymentid) AS "group",  
unnest(array[1, 2, 3]) AS "line item",  
unnest(array[paymentid, '      a', '      c']) AS "paymentid",  
unnest(array[customercode, '      b', '      d']) AS "customercode",  
unnest(array['PREVIOUS BALANCE', 'PAYMENT AMOUNT', 'REMAINING BALANCE']) AS "type",  
unnest(array[previousbalance, paymentamount, remainingbalance]) AS "amount"  
FROM payment_info   
ORDER BY 1, 2 ;  

to generate

 group | line item | paymentid | customercode |       type        | amount 
-------+-----------+-----------+--------------+-------------------+--------
     1 |         1 | PID0001   | CUST024      | PREVIOUS BALANCE  |  10000
     1 |         2 |       a   |       b      | PAYMENT AMOUNT    |   2500
     1 |         3 |       c   |       d      | REMAINING BALANCE |   7500
     2 |         1 | PID0002   | CUST031      | PREVIOUS BALANCE  |   8500
     2 |         2 |       a   |       b      | PAYMENT AMOUNT    |   3500
     2 |         3 |       c   |       d      | REMAINING BALANCE |   5000
     3 |         1 | PID0003   | CUST005      | PREVIOUS BALANCE  |  12000
     3 |         2 |       a   |       b      | PAYMENT AMOUNT    |   1500
     3 |         3 |       c   |       d      | REMAINING BALANCE |  10500

It's a very flexible solution, you know.

Transpose single row with multiple columns into multiple rows of two columns

You need the reverse operation of what crosstab() does. Some call it "unpivot". A LATERAL join to a VALUES expression should be the most elegant way:

SELECT l.*
FROM   tbl     --  or replace the table with your subquery
CROSS  JOIN LATERAL (
   VALUES
  ('registered'       , registered)
, ('downloaded'       , downloaded)
, ('subscribed'       , subscribed)
, ('requested_invoice', requested_invoice)
, ('paid'             , paid)
   ) l(type, value)
WHERE  id = 1;  --  or whatever

You may need to cast some or all columns to arrive at a common data type. Like:

...
   VALUES
  ('registered'       , registered::text)
, ('downloaded'       , downloaded::text)
, ...

Related:

• Postgres: convert single row to multiple rows (unpivot)

For the reverse operation - "pivot" or "cross-tabulation":

• PostgreSQL Crosstab Query

Postgres, split single row into multiple rows

In Postgres, you can efficiently unpivot the columns to rows with a lateral join:

select x.*
from mytable as t
cross join lateral (values 
    (t.age1, t.val1), 
    (t.age2, t.val2), 
    (t.age3, t.val3)
) as x(age, val)

Split value in single row to multiple rows with Postgresql

You could create an array and then use UNNEST():

SELECT UNNEST(ARRAY[1,2,3]);

Postgresql Split single row to multiple rows

Use union:

select id, 1 as "#", name, sub1code, sub1level, sub1hrs
from a_table
union all
select id, 2 as "#", name, sub2code, sub2level, sub2hrs
from a_table
union all
select id, 3 as "#", name, sub3code, sub3level, sub3hrs
from a_table
order by 1, 2;

 id | # |  name  | sub1code | sub1level | sub1hrs 
----+---+--------+----------+-----------+---------
  1 | 1 | Silva  | CHIN     | L1        |      12
  1 | 2 | Silva  | MATH     | L2        |      20
  1 | 3 | Silva  | AGRW     | L2        |      35
  2 | 1 | Perera | MATH     | L3        |      30
  2 | 2 | Perera | ENGL     | L1        |      10
  2 | 3 | Perera | CHIN     | L2        |      50
(6 rows)

The # column is not necessary if you want to get the result sorted by subcode or sublevel.

You should consider normalization of the model by splitting the data into two tables, e.g.:

create table students (
    id int primary key, 
    name text);

create table hours (
    id int primary key, 
    student_id int references students(id),
    code text, 
    level text, 
    hrs int);

Postgres: Convert single rows from multiple tables into multiple rows in a single table

You can use union all:

select p.product_id, v.content_id, v.content_type
from product p cross join lateral
     (values (p.title_id, 'title'),
             (p.description_id, 'description')
     ) v(content_id, content_type)
union all
select product_id, additional_info_id, 'additional_info'
from additional_info ai;

---

Related Topics