Unpivot table in PostgreSQL

Question

I have the below table as a result of SUM(Case....End).

Account            Product_A            Product_B           Product_C
101                 1000                  2000                 3000
102                 2000                  1000                  0
103                 2000                  1000                  0
104                 2000                  1000                  2000

I want to return it back to the original table. Like this:

Account         Product               Qty
101               A                  1000
...               ..                 ....

I think "Unpivot" can do it in MS SQL Server. But I use only PostgreSQL. What is the equivalent of Unpivot in Postgresql that can return/ungroup the above table? Thanks

Answer 1

You can use a lateral join with a values clause:

select t.account, u.product, u.qty
from the_table t
  cross join lateral ( 
     values ('A',product_a), ('B', product_b), ('C', product_c)
  ) as u(product, qty)
order by t.account;

Online example

Answer 2

I think simply unnest will help you:

SELECT Account,
       unnest(array['A', 'B', 'C']) AS Product,
       unnest(array[Product_A, Product_B, Product_C]) AS Qty
FROM test
ORDER BY Account;

FIDDLE

Answer 3

You can dynamically unpivot those columns even if more column (such as Product_D) is added to the table by means of using JSON functions :

SELECT account, UPPER(SPLIT_PART((js).key, '_', 2)) AS Product, (js).value AS Qty 
  FROM( SELECT rj->>'account' AS Account, JSON_EACH_TEXT(rj) AS js
          FROM ( SELECT ROW_TO_JSON(t) AS rj FROM t) AS q1 ) AS q2
 WHERE (js).key != 'account';

Demo

while splitting the column names of the Products depending on the pattern defined for the existing column names (Product_A/B/C) without explicitly specifying each letter.