Creating a Dynamic Crosstab Query
Crosstab queries (also called "pivot tables") in which you know beforehand the number of column values to aggregate by, can easily be performed in T-SQL using CASE statements wrapped in the SUM function. Where things get tricky, however, is when you don't know how many aggregation values exist, and you are required to write a query that will dynamically generate as many aggregation columns as there are aggregation values in the database.
The script I wrote below generates a dynamic crosstab report. It performs quite efficiently, without needing to resort to cursors or temp tables, by generating and executing dynamic SQL. The only catch is that the code generated cannot exceed 8000 characters. (If you need more space for your generated code, you could re-write this script so that it inserts the generated code into a table rather than into a variable).
/* Create and populate tables for testing */
create table employees
(empid int primary key, empname varchar(100))
go
create table sales
(saleid int primary key, empid int foreign key references employees(empid), saledate datetime, saleamount money)
go
insert into employees
select 1, 'joe' union select 2, 'mary' union select 3, 'bob'
go
insert into sales
select 1, 1, getdate(), 10000
union
select 2, 1, getdate() + 30, 10000
union
select 3, 1, getdate() + 60, 10000
union
select 4, 1, getdate() + 90, 10000
union
select 5, 2, getdate(), 10000
union
select 6, 2, getdate() + 30, 10000
union
select 7, 2, getdate() + 60, 10000
union
select 8, 3, getdate(), 10000
union
select 9, 3, getdate() + 30, 10000
union
select 10, 3, getdate() + 120, 10000
go
/* Code for creating crosstab */
declare @counter int, @sql varchar(8000)
select @counter = min(month(saledate)) from sales
set @sql = 'select e.empname, '
while @counter <= (select max(month(saledate)) from sales)
begin
select distinct @sql = @sql + 'sum(case month(s.saledate)' + char(13) +
'when ' + cast(month(saledate) as varchar(2)) +
' then saleamount' + char(13) +
'else 0 end) as [Sales-' + datename(month, saledate) + ']' +
case when @counter = (select max(month(saledate)) from sales)
then ''
else ',' end + char(13)
from sales
where month(saledate) = @counter
select @counter = min(month(saledate)) from sales
where (month(saledate)) > @counter
end
set @sql = @sql + char(13) +
'from employees e
join sales s
on e.empid = s.empid
group by e.empname'
--print @sql --uncomment for debugging
exec(@sql)
