Execute the following T-SQL script in Query Editor to demonstrate the use of CASE function (similar to PIVOT) for the creation of 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