Pivot tables in SQL Server. (A simple sample.)

Pivot tables in SQL Server. (A simple sample.)



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

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 ('JOHNS', 'FRI', 300)

insert into DailyIncome values ('FREDS', 'TUE', 500)

insert into DailyIncome values ('FREDS', 'TUE', 200)

insert into DailyIncome values ('SPIKE', 'MON', 900)

insert into DailyIncome values ('FREDS', 'FRI', 900)

insert into DailyIncome values ('FREDS', 'MON', 500)

insert into DailyIncome values ('JOHNS', 'SUN', 600)

insert into DailyIncome values ('SPIKE', 'FRI', 300)

insert into DailyIncome values ('SPIKE', 'WED', 500)

insert into DailyIncome values ('SPIKE', 'FRI', 300)

insert into DailyIncome values ('JOHNS', 'THU', 800)

insert into DailyIncome values ('JOHNS', 'SAT', 800)

insert into DailyIncome values ('SPIKE', 'TUE', 100)

insert into DailyIncome values ('SPIKE', 'THU', 300)

insert into DailyIncome values ('FREDS', 'WED', 500)

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)

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

 

 

 

 

The short story on how it works using the last query:

 

select * from DailyIncome                                 -- Colums to pivot

pivot (

max (IncomeAmount)                                            -- Pivot on this column

for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) -- Make colum where IncomeDay is in one of these.

as MaxIncomePerDay                                            -- Pivot table alias

where VendorId in ('SPIKE')                               -- Select only for this vendor