Let Us Learn Oracle - Part 24 of N [ Pivot Function of Oracle 11g ]

Purpose: Use for transposing rows into columns

It is exactly the same as Sql Server 2005 Pivot.

Example

This example is taken from TSql Challenge #15.

SQL >	WITH ModCte as
		(
			SELECT	
				RowsValues
				,Col
				,DECODE(MOD(RowsValues,Col),0,0,1) AS ModVal		
				FROM 
				(
					SELECT  RowsValues,Col
					FROM  tblCols
					CROSS JOIN tblRows
				)
		)
		SELECT RowsValues,
					 CASE WHEN	[1]= 0 THEN 'X' ELSE '' END [1]
					,CASE WHEN	[2]= 0 THEN 'X' ELSE '' END [2]
					,CASE WHEN	[3]= 0 THEN 'X' ELSE '' END [3]
					,CASE WHEN	[4]= 0 THEN 'X' ELSE '' END [4]
					,CASE WHEN	[5]= 0 THEN 'X' ELSE '' END [5]
					,CASE WHEN  [6]= 0 THEN 'X' ELSE '' END [6]
					,CASE WHEN  [7]= 0 THEN 'X' ELSE '' END [7]
					,CASE WHEN  [8]= 0 THEN 'X' ELSE '' END [8]
					,CASE WHEN  [9]= 0 THEN 'X' ELSE '' END [9]

		FROM (SELECT RowsValues,Col,ModVal FROM ModCte)
		PIVOT (
				SUM(ModVal) 
				FOR Col 
				IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])
			 );	

RowsValues	1	2	3	4	5	6	7	8	9
---		--	--	--	--	--	--	--	--	--
10		X	X			X				
20		X	X		X	X				
77		X						X		
99		X		X						X
100		X	X		X	X				
101		X								
104		X	X		X				X	

Hope this is useful. Thanks for reading