The Rozenshtein Method - yet another way to pivot data in SQL Server

Wed Jun 14, 2006 by Mladen Prajdić in sql-server

I've stumbled accross this post today that explains the Rozenshtein method of pivoting data.

It's quite interesting. I haven't seen the SIGN() function being used at all in SQL server in practice yet so this is kind of cool if you ask me :)

Legacy Comments


Jon 2006-06-14	re: The Rozenshtein Method - yet another way to pivot data in SQL Server This is one of those things that when you see it you think why couldnt I think of that...then you appreciate it..think its awesome...and end up still not using it :\|. Great post, very cool trick!

Jeff 2006-06-14	re: The Rozenshtein Method - yet another way to pivot data in SQL Server In SQL 7, I remember writing all kinds of funky expressions like that the old ABS(SIGN(..)) trick to return 1 or 0, since you didn't have CASE available back then. It might also be faster than CASE in some situations, I've never checked. Even better is to let some data do the crosstabbing .... create a "matrix" table, put in 1's and 0's in the columns, and then just join to that table and multiply. For example, if you create a "MonthMatrix" table like this: Month, Jan, Feb, Mar, ..., Dec 1,1,0,0,... 2,0,1,0,... 3,0,0,1, ... ... 12,0,0,0,....,1 Then you could crosstab like this: Select ID, SUM(Val * Jan) as Jan, SUM(Val * Feb) as Feb, SUM(Val * Mar) as Mar, .. SUM(Val * Dec) as Dec From YourData inner join MonthMatrix on YourData.Month = MonthMatrix.Month group by ID

Mladen 2006-06-14	re: The Rozenshtein Method - yet another way to pivot data in SQL Server you sure do know your crosstabs jeff :) Nice!

Adam Machanic 2006-06-20	re: The Rozenshtein Method - yet another way to pivot data in SQL Server It is NOT faster than CASE, and the only reason it was invented is because CASE did not exist at the time. Please do not use that technique. All it does is create unmaintainable garbage code.

Mladen 2006-06-20	re: The Rozenshtein Method - yet another way to pivot data in SQL Server hey thanx for clearing that for us Adam. nice to know.

Rob Farley 2006-06-30	re: The Rozenshtein Method - yet another way to pivot data in SQL Server I don't agree that it makes unmaintainable garbage code. There are lots of people that immediately recognise 1-ABS(SIGN(x-y)). It's like counting the occurrences of a substring by using replace and comparing the lengths - the first time you see it, you may have to think about what it's doing - but every time after that, you recognise it immediately and understand. When I see 1-ABS(SIGN(x-y)), I understand that it's generating ones and zeros according to whether x=y or not.

Elena 2008-05-20	re: The Rozenshtein Method - yet another way to pivot data in SQL Server Greatest method ever, and easy to understand and use. Using it all the time when I need to do report by month, or years, or days of week, or come another criteria. Works GREAT!!!!!