Paladin Consultants, LLC

"Good Information is just good business"

JA slide show
Home Sitemap
Print E-mail

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:

 

Total

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Alfreds

4,596

851

0

491

960

0

0

0

1,086

0

1,208

0

0

Ana Tuj

1,403

0

0

514

0

August 16, 2005lign="bottom">

0

0

480

89

0

320

0

Antonio

7,515

660

0

0

881

2,157

2,082

0

0

1,332

0

403

0

Around Hn

13,807

0

735

5,065

492

0

2,143

0

0

0

1,704

1,101

2,567

Berglunds

26,968

3,885

3,398

2,035

0

3,193

1,566

0

3,606

4,879

630

1,459

2,319

Blauer See

3,240

625

0

677

1,144

0

330

464

0

0

0

0

0

Blondesdds

19,088

730

4,049

0

0

0

3,213

1,176

450

2,080

0

7,390

0

Bólido

5,298

0

0

280

0

0

0

0

0

0

982

0

4,036

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

 

 
Bookmark and Share