Pivot table in sql Server

A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. 
UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
But we will see UNPIVOT afterwords.

Example :

Table Design :

VendorId         nvarchar(50)

IncomeDay nvarchar(50)

IncomeAmount int

Insert Some Data :

insert into testdb1.dbo.DailyIncome values ('Nihar', 'WED', 500)
insert into testdb1.dbo.DailyIncome values ('Rohan', 'SAT', 100)
insert into testdb1.dbo.DailyIncome values ('Nihar', 'SAT', 500)
insert into testdb1.dbo.DailyIncome values ('Nihar', 'THU', 800)
insert into testdb1.dbo.DailyIncome values ('Vishal', 'TUE', 600)
.....more...


Query :


select  *  from testdb1.dbo.DailyIncome
pivot (avg (IncomeAmount)
 for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) 
 as AvgIncomePerDay



Output -> 


VendorId MON TUE WED THU FRI SAT SUN
-----------------------------------------------------------------------
Nihar    500 350 500 800 900 500 400  
Rohan 300 600 900 800 300 800 600
Vishal 600 150 500 300 200 100 400