Let us explore UnNest function of PostgreSQL

Niladri.Biswas
Posted by in PostgreSQL category on for Beginner level | Points: 250 | Views : 3112 red flag

In this article we will explore UnNest function of PostgreSQL

Introduction

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

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

Conclusion

so in this article, we have seen as how UnNest function helps in performing the Unpivoting in PostgreSQL.Hope this will be helpful.Thanks for reading.

Page copy protected against web site content infringement by Copyscape

About the Author

Niladri.Biswas
Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)