UnNest function in PostgreSQL

In PostgreSQL, we can perform UnPivoting using the UnNest function. It accepts an array and expands the array items in set of rows.

Syntax: UNNEST(Some array)

Example

Let us consider the below script

Create Table tblUnPivotExample(ItemNo INT, TotalAmt INT,Item1 INT,Item2 INT,Item3 INT,Item4 INT);
Insert Into tblUnPivotExample(ItemNo,TotalAmt,Item1,Item2,Item3,Item4) Values(101,100,10,11,12,14);
Insert Into tblUnPivotExample(ItemNo,TotalAmt,Item1,Item2,Item3,Item4) Values(202,200,20,21,22,24);
Insert Into tblUnPivotExample(ItemNo,TotalAmt,Item1,Item2,Item3,Item4) Values(303,300,30,31,32,34);
Insert Into tblUnPivotExample(ItemNo,TotalAmt,Item1,Item2,Item3,Item4) Values(404,400,40,41,42,44);
Insert Into tblUnPivotExample(ItemNo,TotalAmt,Item1,Item2,Item3,Item4) Values(505,500,50,51,52,54);

We have created a table named as tblUnPivotExample. Projecting the records gives the below output

Select * from tblUnPivotExample;

/*Result*/

ItemNo	TotalAmt	Item1	Item2	Item3	Item4
101	100		10	11	12	14
202	200		20	21	22	24
303	300		30	31	32	34
404	400		40	41	42	44
505	500		50	51	52	54

Now using the UnNest function, we can do the UnPivoting as under

SELECT 
	ItemNo,
	UNNEST(ARRAY['Item1','Item2','Item3','Item4']) AS ItemName, 
	UNNEST(ARRAY[Item1,Item2,Item3,Item4]) AS Amount 
FROM tblUnPivotExample
ORDER BY ItemNo; 

/*Result*/
ItemNo	ItemName	Amount
101	Item1		10
101	Item2		11
101	Item3		12
101	Item4		14
202	Item1		20
202	Item2		21
202	Item3		22
202	Item4		24
303	Item1		30
303	Item2		31
303	Item3		32
303	Item4		34
404	Item1		40
404	Item2		41
404	Item3		42
404	Item4		44
505	Item1		50
505	Item2		51
505	Item3		52
505	Item4		54

ARRAY[Item1,Item2,Item3,Item4] => Returns an array object with the values of the elements Item1,Item2,Item3 and Item4

UNNEST(ARRAY[Item1,Item2,Item3,Item4]) => Breaks the array to a set of rows

To keep it simple, if we perform

Select UnNest(Array['a','b','c','d'])

We will receive

"unnest"
"a"
"b"
"c"
"d"

We can however, write the query as below also

SELECT 
	ItemNo
	,'Item1' AS ItemName
	, Item1 AS Amount
FROM tblUnPivotExample

 UNION ALL 

 SELECT 
	ItemNo
	,'Item2' AS ItemName
	, Item2 AS Amount
FROM tblUnPivotExample

UNION ALL 

 SELECT 
	ItemNo
	,'Item3' AS ItemName
	, Item3 AS Amount
FROM tblUnPivotExample

UNION ALL 

 SELECT 
	ItemNo
	,'Item4' AS ItemName
	, Item4 AS Amount
FROM tblUnPivotExample

ORDER BY ItemNo; 

for obtaining the same result.This query will perform 4 runs on different subqueries on the table tblUnPivotExample one for every column we want to unpivot and yields each record from each of the subqueries in a single table. This is very inefficient as it will perform a table scan 'N' number of times for every column we want to unpivot.

Where as, if we use the UnNest function on array, it scans the table only once

In Sql Server, we do UnPivoting by using UnPivot built in Command which is there since Sql Server 2005.e.g.

Select
		ItemNo
		,ItemName
		,Amount
		From tblUnPivotExample
		UnPivot
		(
			Amount 
			For ItemName IN (Item1,Item2,Item3,Item4)
		)x;

We can however, do the same by using the UNION ALL also

Hope this helps

Thanks for reading