Pivot Function - Transform table rows to colums

PIVOT is very powerful option when data is available in table and requirement is to convert rows in column and display data for those column. One group function is mandatory to use this option.

Syntex -  
SELECT * FROM ( 

SELECT ColumnA,ColumnB,ColumnC,ColumnD 

FROM TABLE_NAME 

WHERE conditions)

PIVOT ( groupFunction(ColumnD) FOR ColumnC in (Values of ColumnC))

Example- assume we have employee expense details for each month. Now requirement is to generate a report which will display each employee’s monthly expenses in column.

Create Table & Insert Values>>

create table test_sandeep(name varchar2(10),dept varchar2(10),month number,amount number);

insert into test_sandeep values('Sandeep','ABC',1,300)

insert into test_sandeep values('Sandeep','ABC',1,120)

insert into test_sandeep values('Sandeep','ABC',2,400)

insert into test_sandeep values('Sandeep','ABC',3,200)

insert into test_sandeep values('Kumar','DEF',2,600)

insert into test_sandeep values('Kumar','DEF',3,700)

insert into test_sandeep values('Kumar','DEF',3,450)

To obtain monthly basis expense amount, we used to write below group by code -

SELECT name,

         dept,

         month,

         SUM (amount)

    FROM test_sandeep

GROUP BY name, dept, month

Using Pivot, we can obtain same data in below manner

SELECT *

  FROM (SELECT name,

               dept,

               month,

               amount

          FROM test_sandeep) PIVOT (SUM (amount) FOR month IN (1 "Jan", 2 "Feb", 3 "Mar"))