Display rows into columns using PIVOT in oracle 11g

We can use PIVOT Keyword to display rows into columns and UNPIVOT Keyword to display column into rows to generate result in crosstab format.

PIVOT:
General Syntax :
SELECT … FROM PIVOT (Pivot_clause Pivot_for_clause Pivot_in_clause) WHERE …

Pivot_clause -> Which defines the columns to be aggregated
Pivot_for_clause->Which defines the columns to be grouped and pivoted
Pivot_in_clause ->Which defines the filter for the columns

Example:
Requirement: Need a query to display number of order created each day of the week and total count of the order for that week.
Expected output :
WEEK_OF__YEAR COUNT_FOR_WEEK 'MON' 'TUE' 'WED' 'THU' 'FRI' 'SAT' 'SUN' 2014-01 10 10 2014-02 12 8 4 2014-05 4 1 3 2014-06 2 2 2014-07 1 1

The table oe_order_headers_all consists order creation date . We have developed below query to get the output (Here we have taken the data only for the year 2014)

Query:
SELECT * FROM ( SELECT TO_CHAR(ooha.creation_date,'YYYY-IW') Week_of__year, TO_CHAR (ooha.creation_date, 'DY') Days_of_week, COUNT (1) Count_for_day, Cfw.Count_for_week FROM oe_order_headers_all ooha, (SELECT Weeks_of_Year, SUM(Count_for_week)Count_for_week FROM (SELECT TO_CHAR (ooha1.creation_date, 'YYYY-IW')Weeks_of_Year, TO_CHAR (ooha1.creation_date, 'DY') Days_of_week, COUNT(1) Count_for_week FROM oe_order_headers_all ooha1 WHERE trunc(ooha1.creation_date) between '1-Jan-2014' and '31-dec-2014' GROUP BY TO_CHAR (ooha1.creation_date, 'YYYY-IW'), TO_CHAR (ooha1.creation_date, 'DY')) GROUP BY Weeks_of_Year Order by 1) Cfw WHERE trunc(ooha.creation_date) between '1-Jan-2014' and '31-dec-2014' AND TO_CHAR(ooha.creation_date,'YYYY-IW')=Cfw.Weeks_of_Year GROUP BY TO_CHAR(ooha.creation_date,'YYYY-IW'), TO_CHAR (ooha.creation_date, 'DY'), Cfw.Count_for_week Order by 1) PIVOT (MAX(Count_for_day) FOR Days_of_week IN('MON','TUE','WED','THU','FRI','SAT','SUN')) ORDER BY 1

Output(Without PIVOT):

Output(With PIVOT):

UNPIVOT:
It is exactly opposite to PIVOT

General Syntax:
SELECT ... FROM ... UNPIVOT [INCLUDE|EXCLUDE NULLS] ( unpivot_clause unpivot_for_clause unpivot_in_clause ) WHERE ...

unpivot_clause: this clause specifies a name for a column to represent the unpivoted measure values.

unpivot_for_clause: the unpivot_for_clause specifies the name for the column that will result from our unpivot query.

unpivot_in_clause: this contains the list of pivoted columns to be unpivoted.