Outside the Box Thinking

I had a problem to solve recently, and essentially it boiled down to a need to do multiple pivots in one statement. After playing with it for a little while and not quite getting it the way I wanted, I turned to Google which led me (seemingly as always) to stackoverflow.com. There I stumbled upon this gem of a Q&A exchange that (almost) perfectly described my problem, and offered an awesome response that I wouldn’t have considered otherwise.

SELECT
field1
, [1] = MAX(CASE WHEN RowID = 1 THEN field2 END)
, [2] = MAX(CASE WHEN RowID = 2 THEN field2 END)
, [3] = MAX(CASE WHEN RowID = 3 THEN field2 END)
, [4] = MAX(CASE WHEN RowID = 4 THEN field2 END)
FROM (
SELECT
field1
, field2
, RowID = ROW_NUMBER() OVER (partition BY field1 ORDER BY field2)
FROM tblname
) SourceTable
GROUP BY
field1

Thanks Peter – I can’t seem to contact you directly for the contribution.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *