Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 3, 2001

Simulating CROSS-TAB queries using CASE operator

By Maxim Smirnov

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date