What is pivot and What is the use of Pivot?

Main reason for using Pivot is we want to transfer our data from row level to column level.

Pivot is one of the SQL operators which is used to turn the unique data from one column into multiple columns in the output. This is also mean by transforming the rows into columns. 

The columns in above query Jan,Feb,Apr are produced by pivot query.

After Pivoting the columns are selecting by outer select query.

Alternate Solution:Instead of using pivot we can also use dynamic pivot.

Pivot is used to turn unique values from one column into multiple columns in the output . That is, it rotates a table. It is a sql server operator.

Example:

There is a table which has columns . like shown below.

Three sales agents Jiraiya, Vegeta and Nico Robin in three different countries US, Uk and japan.

So a query which gives as result like total amount by country and by agent

We can use groupby query to get an output.

Which gives an output as follows.

This gives a Basic date output . But we can use Pivot operation to display it in cross tab format.

Using Pivot we can convert column values into column names in the output.

The columns in the above query Japan, US and UK are the pivoted columns produced by the Pivot operator.

The pivot operator is performing a sum aggregate function on the Amount column(we want the total sales amount).

After pivoting the columns they are selected by the outer select query.

This is the expected output.

The PIVOT clause allows you to write a cross-tabulation. This means that you can aggregate your results and rotate rows into columns.