Let Us Learn Oracle - Part 25 of N [ UnPivot Function of Oracle 11g ]

Purpose: Use for transposing columns into rows

It is exactly the same as Sql Server 2005 UnPivot.

Example

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

ItemNo	ItemName	Amount
------	--------	-------
101	Item1		10
101	Item2		90
101	Item3		60
101	Item4		40
202	Item1		25
202	Item2		25
202	Item3		30
202	Item4		20

The default behaviour of UnPivot is to exclude the null rows. But we can handle so by using the INCLUDE NULLS

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

Hope this is useful. Thanks for reading