DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to design crosstab report using CASE?

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

....

*/

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE