Query for crosstab view

Question

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|
---------------

Answer 1

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:

• Convert one row into multiple rows with fewer columns
• SELECT DISTINCT on multiple columns
• Postgres: convert single row to multiple rows (unpivot)

Note: You added the tag crosstab. 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:

• PostgreSQL Crosstab Query