SQL Advanced

The Rozenshtein method for producing crosstabs

Added 2002-08-10

Despite its title, How to emulate the Oracle decode function/crosstab, part of the Sybase Adaptive Server Enterprise FAQ, requires neither Sybase nor Oracle SQL. The article shows how to produce crosstab results using either the standard SQL CASE construct, or a fiendishly clever alternative, which, the article emphasises, does not produce easily understood SQL statements.

SELECT STUDENT_ID,
, (1- ABS( SIGN( ISNULL( 101 - COURSE_ID, 1)))) COURSE_101
, (1- ABS( SIGN( ISNULL( 105 - COURSE_ID, 1)))) COURSE_105
, (1- ABS( SIGN( ISNULL( 201 - COURSE_ID, 1)))) COURSE_201
, (1- ABS( SIGN( ISNULL( 210 - COURSE_ID, 1)))) COURSE_210
, (1- ABS( SIGN( ISNULL( 300 - COURSE_ID, 1)))) COURSE_300
GROUP BY STUDENT_ID
ORDER BY STUDENT_ID

This will easily work in other databases, too (remember to use COALESCE instead of ISNULL).

Update February 2004

Another example is given in Stephen Forte's The Rozenshtein Method.