Paladin Consultants, LLC

David Rozenshtein is a Russian-born mathematics professor from Rutgers University who defined a neat way to crosstab and pivot data from a SQL table. This is an excellent way to produce static crosstab (when you know beforehand all of the possible values ) queries in SQL Server and other ASNI compliant client server databases.

As an example, we use the Northwind database, and make a crosstab of the order data by company and by month. Rownames would be CompanyName, and ColumnNames would be Months. The data in the cells would be sum of order amounts by Company by month.  

Output from such a query would look like this:

Using Rozenshtein's method, the SQL query to accomplish this would be the following:

SELECT CompanyName, SUM((UnitPrice*Quantity)) As TotalAmt,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-1)))) AS Jan,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-2)))) AS Feb,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-3)))) AS Mar,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-4)))) AS Apr,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-5)))) AS May,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-6)))) AS Jun,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-7)))) AS Jul,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-8)))) AS Aug,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-9)))) AS Sep,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-10)))) AS Oct,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-11)))) AS Nov,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-12)))) AS Dec
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
Group By Customers.CompanyName

What you need to do is to create an expression (the one that begins with ‘(1-‘, and ends with all of the parentheses) that evaluates to ‘1' when the condition you want is ‘False', and ‘0' when it is True. That way the whole multiplication creates a 0 for a SUM when conditions are not as expected, and the appropriate quantity to aggregate when they are. The only way to get included will be for the expression following the ‘1-‘ to be equal to ‘0' (zero).

Notice in the example that each ‘DatePart' result has subtracted from it the requisite month. Any other value (than the searched for month) that the DatePart yields will be converted to either a +1 or –1 by the SIGN function and then to a 1 by the ABS function. Further processing will add 0 to the SUM for that month because the factor will resolve to zero. Only the sought-after month will result in a 1 factor because the ‘ABS(SIGN(DatePart…' will resolve to zero, which, when subtracted from 1 will yield a 1 factor.

Try it for yourself. Use the Query Analyzer and substitute any date in the format ‘03/11/1992' in the following expression And verify that only March dates resolve to ‘1':

(1-ABS(SIGN(DatePart(mm,'03/11/1992')-3)))

Metro NY / NJ SQL Server Consultants

We specialize is custom database software. Call us for a free consultation (973) 635 0080

Computer Consultants: Database Development
Computer Programming: Web Site Services
Computer Programming: Custom Software
Computer Consulting: IT Consulting
Paladin Consultants, LLC Home Page
Computer Consulting: Contact Us