T-SQL Pivot Tables - Cross Tab Reports
Creating a Cross Tab Report in a Query with T-SQL in SSMS
This article describes how to create a Cross Tab Query (as in MS Access or SSAS) also known as a Pivot Table in Excel. SQL Server 2005 and later make this easy using PIVOT which will be demonstrated using a T-SQL Query. Performing this task in SQL Server 2000 is not quite as straightforward, but it is possible as also shown in this article.
Cross Tab (Pivot Table) Report Data for SQL Server 2005, 2008, 2008R2, 2012
To begin, we will create a table and populate some data that we can perform the query upon. Data content credit and Pivot query credit goes to the article Pivot tables in SQL Server. A simple sample written by Michael Aspengren. It was the perfect solution to an answer I needed with the exception that I needed it to work on SQL Server 2000...
To start, create the table
create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)
Create the Insert statements to populate the new table
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)
The PIVOT applied to the query to get the Cross Tab data in SQL Server 2012
select * from DailyIncome pivot(avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay
The result set shows us the Average data for each of the VendorIds in the table by Days of the Week
To prove it, I created an Excel Pivot Table using the same data provided
Data in Excel
Pivot Table in Excel from Data
SQL Server 2000 Pivot Table or Cross Tab Reports
Unfortunately, PIVOT was introduced in SQL Server 2005, so it cannot be used on a 2000 Server. A different way of getting the data is required.
A test using the CUBE keyword to get all of the rows gets the data, but also retrieves all permutations and generates all of the aggregate data in rows with the extra super aggregate data and cross tab data for NULL VendorIds and IncomeDays that is not needed
select VendorId, IncomeDay,
Avg(incomeamount) as AvgDayIncome
FROM DailyIncome
group by VendorId, IncomeDay
with cube
order by VendorId, IncomeDay
Using ROLLUP goes a little better - the result set still has super aggregate rows, but does not have the extra cross tab permutations of CUBE.
select VendorId, IncomeDay, Avg(incomeamount) as AvgDayIncome
FROM DailyIncome
group by rollup (VendorId, IncomeDay)
order by VendorId, IncomeDay
The data in the above result set still does not have the same columnar format that the Pivot Table structure has.
The sample query below will get you the correct data for SUM and is a good candidate for using in SQL Server 2000 to generate a Pivot Table without PIVOT. The PIVOT query is also included as proof.
--pivot table
select * from DailyIncome
pivot(Sum (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay
--T SQL query
select vendorid,
SUM(case when incomeday = 'MON' THEN incomeamount ELSE 0 END) as MON,
SUM(case when incomeday = 'TUE' THEN incomeamount ELSE 0 END) as TUES,
SUM(case when incomeday = 'WED' THEN incomeamount ELSE 0 END) as WED,
SUM(case when incomeday = 'THU' THEN incomeamount ELSE 0 END) as THURS,
SUM(case when incomeday = 'FRI' THEN incomeamount ELSE 0 END) as FRI,
SUM(case when incomeday = 'SAT' THEN incomeamount ELSE 0 END) as SAT,
SUM(case when incomeday = 'SUN' THEN incomeamount ELSE 0 END) as SUN
from DailyIncome
group by vendorid
However, this will not work exactly as written for the AVG function as shown here
select * from DailyIncome
pivot(AVG (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay
select vendorid,
AVG(case when incomeday = 'SUN' THEN incomeamount ELSE 0 END) as SUN,
AVG(case when incomeday = 'MON' THEN incomeamount ELSE 0 END) as MON,
AVG(case when incomeday = 'TUE' THEN incomeamount ELSE 0 END) as TUES,
AVG(case when incomeday = 'WED' THEN incomeamount ELSE 0 END) as WED,
AVG(case when incomeday = 'THU' THEN incomeamount ELSE 0 END) as THURS,
AVG(case when incomeday = 'FRI' THEN incomeamount ELSE 0 END) as FRI,
AVG(case when incomeday = 'SAT' THEN incomeamount ELSE 0 END) as SAT
from DailyIncome
group by vendorid
Removing the AVG function from the CASE statements shows us what is happening. The number of rows that the Average function is using for division is now inflated artificially by the query the way that it is presently written. FREDS now has all of this daily averages divided by 8 rather than 1 day that he actually had income.
select vendorid,
(case when incomeday = 'SUN' THEN incomeamount ELSE 0 END) as SUN,
(case when incomeday = 'MON' THEN incomeamount ELSE 0 END) as MON,
(case when incomeday = 'TUE' THEN incomeamount ELSE 0 END) as TUES,
(case when incomeday = 'WED' THEN incomeamount ELSE 0 END) as WED,
(case when incomeday = 'THU' THEN incomeamount ELSE 0 END) as THURS,
(case when incomeday = 'FRI' THEN incomeamount ELSE 0 END) as FRI,
(case when incomeday = 'SAT' THEN incomeamount ELSE 0 END) as SAT
from dailyincome
order by vendorid
A change in the Syntax will fix this issue. Removing the Else 0 portion of the CASE Statement will consolidate the rows as the Null values are eliminated by aggregating the data with the Group By Clause and AVG function. A warning often seen on the Messages tab in SSMS.
select vendorid ,
'SUN' = avg( CASE incomeday WHEN 'SUN' THEN incomeamount END),
'MON' = avg( CASE incomeday WHEN 'MON' THEN incomeamount END),
'TUE' = avg( CASE incomeday WHEN 'TUE' THEN incomeamount END),
'WED' = avg( CASE incomeday WHEN 'WED' THEN incomeamount END),
'THU' = avg( CASE incomeday WHEN 'THU' THEN incomeamount END),
'FRI' = avg( CASE incomeday WHEN 'FRI' THEN incomeamount END),
'SAT' = avg( CASE incomeday WHEN 'SAT' THEN incomeamount END)
from dailyincome
group by vendorid
Note regarding CUBE and ROLLUP - the usage of CUBE and ROLLUP above will be deprecated in a future version of SQL Server. The correct syntax for Cube is now:
select VendorId, IncomeDay, Avg(incomeamount) as AvgDayIncome
FROM DailyIncome
group by CUBE (VendorId, IncomeDay)
order by VendorId, IncomeDay
The correct syntax for Rollup is:
select VendorId, IncomeDay, Avg(incomeamount) as AvgDayIncome
FROM DailyIncome
group by ROLLUP (VendorId, IncomeDay)
order by VendorId, IncomeDay
Both versions written will produce the same results shown in this article. The usage above was used as the annotated usage here will not work in SQL Server 2000