CODEWITHSUNDEEP
sqlpostgresqlcrosstabunpivot
zakaria
zakaria

Reputation: 436

Query for crosstab view

I have a table in PostgreSQL like below:

--------------------------------------------------------------
Item1 | Item2 | Item3 | Item4 |Value1| Value2| Value3| Value4|
--------------------------------------------------------------

I want a query which will show this table like below:

ItemHead| ValueHead
---------------
Item1 | Value1|
---------------
Item2 | Value2| 
----------------
Item3 | Value3| 
----------------
Item4 | Value4|
---------------

Upvotes: 0

Views: 696

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659347

Use a single SELECT with a LATERAL join to a VALUES expression. That's shorter and faster than multiple SELECT statements:

SELECT v.*
FROM   tbl, LATERAL (
   VALUES
      (item1, value1)
    , (item2, value2)  -- data types must be compatible
    , (item3, value3)
    , (item4, value4)
   ) v ("ItemHead", "ValueHead");  -- your desired column names

Related:

Note: You added the tag . But the Postgres function crosstab() from the additional tablefunc module is used for pivoting, while this task is the opposite, sometimes referred to as unpivoting. Related:

Upvotes: 3

Radim Bača
Radim Bača

Reputation: 10711

Simply use UNION ALL

SELECT item1, value1 FROM your_tab
UNION ALL
SELECT item2, value2 FROM your_tab
UNION ALL
SELECT item3, value3 FROM your_tab
UNION ALL
SELECT item4, value4 FROM your_tab

Upvotes: 0

Related Questions