Pivot in Oracle for Dynamic Columns

Pivot in SQL refers to the change of rows into columns based on specific constraints.
An example of pivot could be the transpose of matrix.
Example:
a matrix containing day as 1st row and sales as 2nd row for  specific day

sun    10
mon  20
tue    30
The transpose of the above matrix would be

sun mon tue
10  20  30

This is an example scenario of pivot in sql.

Now, Lets get on to the actual tables in database and see how we can use pivot .

Starting with a fictional scenario,
assume a table which contains two columns DAYS and SALES.


CREATE TABLE TEST.DAILY_SALES
(
  DAYS   VARCHAR2(10),
  SALES  NUMBER
)

insert into DAILY_SALES('SUN',10);
insert into DAILY_SALES('MON',20);
insert into DAILY_SALES('TUE',15);
insert into DAILY_SALES('WED',25);
insert into DAILY_SALES('THU',10);
insert into DAILY_SALES('FRI',30);
insert into DAILY_SALES('SAT',5);

Now when we execute the following select statement
select * from  TEST.DAILY_SALES
we get the output as




Now lets select data using pivot
run the following script :

 select * from  TEST.DAILY_SALES
pivot(max(SALES) for days in (
           'SUN','MON','TUE','WED','THU','FRI','SAT'
            )
     )
or using alias for columns

select * from  TEST.DAILY_SALES
pivot(max(SALES) for days in (
            'SUN' AS "SUN",'MON' AS "MON",'TUE' AS "TUE",'WED' AS "WED",'THU' AS "THU",'FRI' AS "FRI",'SAT' AS "SAT"
            )
     )

The out put result is as :






Here we get the different days as column names and sales as data for the specific column.

I hope I could clarify purpose of pivot and its basic syntax is clear by now.