I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

The Rozenshtein Method - yet another way to pivot data 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 :)

 

Print | posted on Wednesday, June 14, 2006 11:20 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# 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!
6/14/2006 5:53 PM | Jon
Gravatar

# 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
6/14/2006 7:11 PM | Jeff
Gravatar

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

you sure do know your crosstabs jeff :)

Nice!
6/14/2006 7:25 PM | Mladen
Gravatar

# 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.
6/20/2006 7:41 AM | Adam Machanic
Gravatar

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

hey thanx for clearing that for us Adam.
nice to know.
6/20/2006 5:19 PM | Mladen
Gravatar

# 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.
6/30/2006 8:46 AM | Rob Farley
Gravatar

# T-SQL: The Rozenshtein Method

7/8/2006 6:38 PM | Anatoly Lubarsky
Gravatar

# 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!!!!!
5/20/2008 7:15 PM | Elena
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET