Simulating CROSS-TAB queries using CASE operator

This example uses data from NORTHWIND database that comes with every SQL Server installation.

Two tables will be used in the query: EMPLOYEES and ORDERS.

Let’s say that data analyst is preparing a presentation and interested in how many orders were placed by every particular employee during each year of operation.

Simple GROUP BY query will give quick answer to that question:

SELECT E.LastName, E.FirstName,


‘Year’ = DATEPART(year,OrderDate),


’OrdersPlaces’ = COUNT(*)

FROM EMPLOYEES E,

ORDERS O

WHERE E.EmployeeID = O.EmployeeID

GROUP BY E.LastName, E.FirstName,

DATEPART(year,OrderDate)

An the result will be the following:

LastName

FirstName

Year

OrdersPlaced

Buchanan

Steven

1996

11

Buchanan

Steven

1997

18

Buchanan

Steven

1998

13

Callahan

Laura

1996

19

Callahan

Laura

1997

54

Callahan

Laura

1998

31

Davolio

Nancy

1996

26

Davolio

Nancy

1997

55

Davolio

Nancy

1998

42

This way of presenting data is absolutely correct and completely answers the questions but not convenient for analysis and definitely cannot be placed into presentation.

The best way of presenting such results would be simulating a cross tab query using CASE operator. For each record CASE will check if it falls under specified condition (e.g. year of order date is 1996) and issue 1 (true) or 0 (false). After that SUM operator will calculate the total number of orders placed grouping it by employee last and first name.

 

 

SELECT E.LastName, FirstName,

’1996′ = SUM(CASE WHEN DATEPART(year,OrderDate) = 1996 THEN 1 ELSE 0 END),

’1997′ = SUM(CASE WHEN DATEPART(year,OrderDate) = 1997 THEN 1 ELSE 0 END),

’1998′ = SUM(CASE WHEN DATEPART(year,OrderDate) = 1998 THEN 1 ELSE 0 END)

FROM EMPLOYEES E,

ORDERS O

WHERE E.EmployeeID = O.EmployeeID

GROUP BY

E.LastName,

E.FirstName

 

The result of the that query:

LastName

FirstName

1996

1997

1998

Fuller

Andrew

16

41

39

Dodsworth

Anne

5

19

19

Leverling

Janet

18

71

38

Callahan

Laura

19

54

31

Peacock

Margaret

31

81

44

Suyama

Michael

15

33

19

Davolio

Nancy

26

55

42

King

Robert

11

36

25

Buchanan

Steven

11

18

13

Fuller

Andrew

16

41

39

Dodsworth

Anne

5

19

19

This way is more natural and convenient. Information is not duplicated and result can be pasted directly into the presentation document.

If you have any questions or would like to advise on how to improve this method please feel free to contact me at msmirnov@swynk.com.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles