Rozenshtein Method
Every once in a while you need a query to return results in a crosstab format. In the past I’ve always approached this in the typical way of using CASE statements to check the key and then outputing the value or returning zero / an empty space. This works well but it can get verbose at times.
Today I ran across a posting on the Rozenshtein Method. It’s a really amazing approach and it’s SQL 92 compliant. The post can be found here:
http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8
I will not describe it since that’s already done, but I highly recommend you check it out.
Although the example uses the case of crosstabing based on integers, this same method can be used effectively with characters, since every character has an ANSI equivalent. At a client site we used this method to build a crosstab of racial ethnic diversity counts. The end result is kind of a mess, but it works great. For your amusement, here’s a snippet:
SELECT l.id, SUM(l.TotalCount) AS total_count, SUM(l.FemaleCount) AS FemaleCount,
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-801)))) AS "801",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-802)))) AS "802",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-803)))) AS "803",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-804)))) AS "804",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-805)))) AS "805",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-806)))) AS "806",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-807)))) AS "807",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-808)))) AS "808",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-809)))) AS "809",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-810)))) AS "810",
SUM((e.ethniccount)*(1-ABS(SIGN(CONVERT(INT, e.ethniccode)-811)))) AS "811"
FROM RacialEthnicProfilingOnAirlines l
INNER JOIN RacialEthnicCounts e
ON l.id = e.id
INNER JOIN Airlines a
ON l.id = a.id
WHERE a.id IN (SELECT id FROM [Airlines] WHERE [Name] = 'UNTIDY')
GROUP BY l.id
ORDER BY l.id