Thursday, December 15, 2011

Rows into Columns in Oracle

The oracle database 11g has some new features such as PIVOT and UNPIVOT clauses,By using these clauses we can rotate the rows into column in output from a query.PIVOT and UNPIVOT are useful to see in large amount of data such as trends data over a period of time.Hera Ive shown a simple query to get the income details for three in year

Example:
SELECT *FROM
(SELECT month,id,income FROM all_business WHERE year="2011" and id IN(1,2,3))
PIVOT
(SUM(income) FOR month IN(1 AS JUNE,2 AS JULY,3 AS AUGUST)) ORDER BY id

CREATE TABLE ALL_Income AS
SELECT *FROM
(SELECT month,id,income FROM all_business WHERE year="2011" and id IN(1,2,3))
PIVOT
(SUM(income) FOR month IN(1 AS JUNE,2 AS JULY,3 AS AUGUST)) ORDERBY id


SELECT *FROM ALL_Income
UNPIVOT(income FOR month IN(JUNE,JULY,AUGUST))ORDER BY id

Post a Comment

Bel