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.
,  = MAX(CASE WHEN RowID = 1 THEN field2 END)
,  = MAX(CASE WHEN RowID = 2 THEN field2 END)
,  = MAX(CASE WHEN RowID = 3 THEN field2 END)
,  = MAX(CASE WHEN RowID = 4 THEN field2 END)
, RowID = ROW_NUMBER() OVER (partition BY field1 ORDER BY field2)
Thanks Peter – I can’t seem to contact you directly for the contribution.