T-SQL: The Rozenshtein Method

The Rozenshtein Method is a method used in T-SQL for crosstab queries and pivoting. Stephen Forte explains:

"This method uses Boolean aggregates, so that each column has a numeric expression that resolves each row as a zero or one and that value (0 or 1) is multiplied by your numeric expression (Like TotalSales or (UnitPrice*Quantity)."

The query uses the following logical expression:

ABS(SIGN(expression))

SIGN evaluates the expression to 1 or 0 or -1, ABS reduces it to 0 or 1, so we get aka boolean aggregate. Impressive. It reminds me using ISNULL for conditional search queries.

[ via Mladen ]

Related Posts:

• Always Use Stored Procedures in MSSQL
• MSSQL 2005 Service Pack 1 is available
• Enterprise Library: Bug with decimal in DataAccess block
• Microsoft Query Optimization Team is Blogging
• MSSQL 2005: Paging with ROW_NUMBER is the fastest
• MSSQL: Managing Schema Changes on Large Tables
• The most important thing in SQL
• T-SQL: Get only Date from DateTime
• T-SQL: Date Ranges
• Date Processing - T-SQL or SQLCLR ?