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,
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':
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