I want some Moore

# 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!

# 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

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

you sure do know your crosstabs jeff :)

Nice!

# 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.

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

hey thanx for clearing that for us Adam.
nice to know.

# 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.

# T-SQL: The Rozenshtein Method

# 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!!!!!