|
Execute the following Microsoft SQL Server 2008
T-SQL scripts in SSMS Query Editor to demonstrate the use of static and dynamic CASE function (similar to PIVOT) for the creation of a crosstab report:
-- Crosstab query with CASE function - Creating a Crosstab Report
use AdventureWorks2008
go
select ProductName=p.Name,
'$'+convert(varchar,sum (case when year(TransactionDate)=2003
then ActualCost end),1) as Y2003,
'$'+convert(varchar,sum (case when year(TransactionDate)=2004
then ActualCost end),1) as Y2004,
'$'+convert(varchar,sum (case when year(TransactionDate)=2005
then ActualCost end),1) as Y2005
from Production.TransactionHistory th
join Production.Product p
on p.ProductID = th.ProductID
group by p.Name
order by p.Name
go
------------
-- Dynamic CASE function - dynamic crosstab report
use AdventureWorks2008
go
declare @SQL nvarchar(max), @YYYY char(4)
set @SQL='select ProductName=p.Name'+CHAR(10)
declare curYear cursor for -- T-SQL cursor
select distinct YYYY=CONVERT(varchar, year(OrderDate))
from Sales.SalesOrderHeader
order by YYYY
open curYear
fetch next from curYear into @YYYY
while (@@FETCH_STATUS = 0)
begin
select distinct @SQL=@SQL +',' +
'''$''+convert(varchar,sum (case when year(TransactionDate)='+
@YYYY+ +CHAR(10)+
'then ActualCost end),1) as Y'+@YYYY+CHAR(10)
fetch next from curYear into @YYYY
end -- while
close curYear
deallocate curYear
set @SQL=@SQL+
' from Production.TransactionHistory th
join Production.Product p
on p.ProductID = th.ProductID
group by p.Name
order by p.Name'
print @SQL -- test & debug
/*
select ProductName=p.Name
,'$'+convert(varchar,sum (case when year(TransactionDate)=2001
then ActualCost end),1) as Y2001
,'$'+convert(varchar,sum (case when year(TransactionDate)=2002
then ActualCost end),1) as Y2002
,'$'+convert(varchar,sum (case when year(TransactionDate)=2003
then ActualCost end),1) as Y2003
,'$'+convert(varchar,sum (case when year(TransactionDate)=2004
then ActualCost end),1) as Y2004
from Production.TransactionHistory th
join Production.Product p
on p.ProductID = th.ProductID
group by p.Name
order by p.Name
*/
EXEC sp_executeSQL @SQL -- Dynamic SQL execution
GO
/*
ProductName Y2001 Y2002 Y2003 Y2004
....
Road-350-W Yellow, 44 NULL NULL $139,345.10 $309,580.18
Road-350-W Yellow, 48 NULL NULL $238,955.08 $466,479.50
....
*/
|
