Loading ...
Global Do...
News & Politics
5
0
Try Now
Log In
Pricing
USING THE CASE FUNCTION TO MAKE A CROSS-TAB REPORT With SQL SERVER 2000 As a programmer, I often get requests to make a report as a cross-tab form. Cross-tab report is also known as the PivotTable, or rotating data due to data playback, so the data presented in a column as a row or vice versa. This report form is often used by management to make decisions, because it is most easier way to read the data. But on the other hand, it also became another problem for novice programmers who work at the company where I worked. On this occasion I'll try to guide how to solve this problem by giving examples of common problems that may often faced by novice programmers. Well, let's get started! For example, if there is a SalesQuarter table that contains sales data looks like this: Region Year Quarter Quantity Region A 2009 1 1000 Region A 2009 2 2000 Region A 2009 3 3000 Region A 2009 4 4000 Region B 2009 1 1500 Region B 2009 2 2500 Region B 2009 3 3500 Region B 2009 4 4500 Report desired by management is as follows: Region Year Quarter 1 Quarter2 Quarter3 Quarter4 Region A 2009 1000 2000 3000 4000 Region B 2009 1500 2500 3500 4500 Using CASE function Cases making cross-tab report can be solved by using CASE function in SQL Server 2000. Case will evaluate a list of conditions and returns one of multiple possible result expressions. Before we go any further, there are two types of Case functions that you must know: 1. Simple Case Function 2. Searched Case Function Let us know about each of these types, 1. The Simple Case Function This function will compare a simple expression to specify another expression as a result of the change. For example, a sales_category table is as follows: Month ItemNum Quantity January 101 100 January 102 200 January 103 300 February 101 400 February 102 500 February 103 600 Values for 101, 102, 103 in “ItemNum” column states to Laptop, Computer, and Printer The following SQL statement contains a "simple case function": SELECT Month, ItemName = CASE ItemNum When ‘101’ then ‘Laptop’ When ‘102’ then ‘Computer’ When ‘103’ then ‘Printer’ END, Quantity FROM sales_category The statement above will produce the following records: Month ItemName Quantity January Laptop 100 January Computer 200 January Printer 300 February Laptop 400 February Computer 500 February Printer 600 Note: CASE ItemNum When ‘101’ then ‘Laptop’ is a simple case function that will assess the expression is the value in column ItemNum = '101 '. If true, then the result will be 'Laptop'. The statement of ItemName = CASE ItemNum ... will produce a new column called ItemName 2. The Searched Case Function This function will evaluate a set of boolean expressions to determine the outcome. Example: We are still using the sales_category table. The report will show a column contains the value of the category of small, medium, or large amount of sales stated. Small category is intended for sale under 300. Between 300 and 400 is medium. Over 400 included in the large amount of category. Consider the following SQL statement Case Searched Function that contains the boolean expressions: SELECT Month, ItemNum, Quantity, CategoryAmount = CASE WHEN Quantity < 300 then ‘Small’ WHEN Quantity >= 300 and Quantity <= 400 then ‘Medium’ WHEN Quantity > 400 then ‘Large’ END FROM sales_category The statement above will produce the following records: Month ItemNum Quantity CategoryAmount January 101 100 Small January 102 200 Small January 103 300 Middle February 101 400 Middle February 102 500 Large February 103 600 Large Note: Statement of CategoryAmount = CASE ... will produce a new column called CategoryAmount. Creating a Cross-Tab sales reports Return to cases on sales reported arising from the SalesQuarter table. We will try to run the following SQL statement: SELECT Region, [Year], CASE Quarter WHEN 1 THEN Quantity ELSE 0 END as Quarter1, CASE Quarter WHEN 2 THEN Quantity ELSE 0 END as Quarter2, CASE Quarter WHEN 3 THEN Quantity ELSE 0 END as Quarter3, CASE Quarter WHEN 4 THEN Quantity ELSE 0 END as Quarter4 FROM SalesQuarter The expression of CASE Quarter WHEN 1 THEN Quantity ELSE 0 END as Quarter1, sates of: Generate new column called Quarter1 Quarter1 column contains the value derived from the Quantity column on the condition that the value of the column quantity equal to 1 The other columns (Quarter2, Quarter3 and Quarter4) made in the same way. The statement above will produce the following records: Region Year Quarter 1 Quarter2 Quarter3 Quarter4 Region A 2009 1000 0 0 0 Region A 2009 0 2000 0 0 Region A 2009 0 0 3000 0 Region A 2009 0 0 0 4000 Region B 2009 1500 0 0 0 Region B 2009 0 2500 0 0 Region B 2009 0 0 3500 0 Region B 2009 0 0 0 4500 The results above do not match the desired format, because for a different quarter still made a new row. Rows that contain data (Region A, 0, 2000, 0, 0) appears on the second row, it should be in the same row with the first row. To achieve this, the SQL statement must be combined with grouping functions that will make a few lines in the same group into one line. The statement becomes: SELECT Region, [Year], SUM(CASE Quarter WHEN 1 THEN Quantity ELSE 0 END) as Quarter1, SUM(CASE Quarter WHEN 2 THEN Quantity ELSE 0 END) as Quarter2, SUM(CASE Quarter WHEN 3 THEN Quantity ELSE 0 END) as Quarter3, SUM (CASE Quarter WHEN 4 THEN Quantity ELSE 0 END) as Quarter4 FROM SalesQuarter GROUP BY Region, [Year] Note: The statement CASE Quarter WHEN 1 THEN Quantity ELSE 0 END as Quarter1, Added with SUM aggregate function to be: SUM(CASE Quarter WHEN 1 THEN Quantity ELSE 0 END) as Quarter1. The GROUP BY Region, Year clause will be grouped the value in one region and year Now, we have the desired report format. Region Year Quarter 1 Quarter2 Quarter3 Quarter4 Region A 2009 1000 2000 3000 4000 Region B 2009 1500 2500 3500 4500 Well, you already know how to create cross-tab report using CASE function. Of course you can try to resolve other cross-tab cases by using the way I've mentioned to you. At the bottom line, hopefully this tutorial is useful.
