SQLUSA

Microsoft SQL Server 2005 Best Practices

How to swap rows to columns?

 

Execute the following script in Query Editor to create a cross tabulation of total cost for all products by fiscal year. The year of TransactionDate is swapped across into columns. The aggregate function SUM calculates the total cost.


use AdventureWorks
go
select ProductName=p.Name,
'$'+convert(varchar,sum (case when year(TransactionDate)=2000 then ActualCost end),1) as Y2000,
'$'+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,
'$'+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

 

SQLUSA - The Best SQL Server 2005 Training in the World
 
 
SQLUSA.com Home Page