I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net

posts - 199, comments - 2249, trackbacks - 33

My Links

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 NT Conference
Welcome to my blog.

Search this Blog

Users Online:

Article Categories

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

<< SQL Server 2005 Output Clause | Home | VS 2005 and SQL Server Management Studio Keyboard Shortcuts - pure magic >>

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 :)

Feedback

# 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

# 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

# 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

# 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

# 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

# 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

# T-SQL: The Rozenshtein Method

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

# 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