What is the use of SQL PIVOT ?

 Posted by Bharathi Cherukuri on 7/24/2012 | Category: Sql Server Interview questions | Views: 1063 | Points: 40
Answer:

By using this PIVOT operator, you can rotate the rows in a table to seperate columns.
The main advantage of this operator is that, it takes a normalized table into consideration and will convert it into a new table in which the values of the columns are derived from the original table values.

Example:

create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)


The Vendor id, the day of the week they are referring to and what the income on that day was.
So let’s fill it with some data.
insert into DailyIncome values ('SPIKE', 'FRI', 100)

insert into DailyIncome values ('SPIKE', 'MON', 300)
insert into DailyIncome values ('FREDS', 'SUN', 400)
insert into DailyIncome values ('SPIKE', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'TUE', 200)
insert into DailyIncome values ('JOHNS', 'WED', 900)
insert into DailyIncome values ('SPIKE', 'FRI', 100)
insert into DailyIncome values ('JOHNS', 'MON', 300)
insert into DailyIncome values ('SPIKE', 'SUN', 400)
---
insert into DailyIncome values ('SPIKE', 'SAT', 100)
insert into DailyIncome values ('FREDS', 'SAT', 500)
insert into DailyIncome values ('FREDS', 'THU', 800)
insert into DailyIncome values ('JOHNS', 'TUE', 600)

Now, if we select out the flat data that we have, we will get the following:

VendorId IncomeDay IncomeAmount
---------- ---------- ------------
SPIKE FRI 100
SPIKE MON 300
FREDS SUN 400
SPIKE WED 500
SPIKE TUE 200
...
SPIKE WED 500
FREDS THU 800
JOHNS TUE 600


To find the average for each vendor, run this query:

select * from 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
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
FREDS 500 350 500 800 900 500 400
JOHNS 300 600 900 800 300 800 600
SPIKE 600 150 500 300 200 100 400


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response