Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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

PIVOT results SSMS

To prove it, I created an Excel Pivot Table using the same data provided

Data in Excel

Excel Pivot Data Table

Pivot Table in Excel from Data

Excel Pivot Table

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

Cube SQL Data

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

ROLLUP SQL Result

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

PIVOT vs GROUP By Aggregation

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

AVG Function GROUP BY Case

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

Results of Case Statement with 0 Else Clause

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

9

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