ajames 36824 (2/22/2013)
Jeff Moden (2/22/2013)
ajames 36824 (2/22/2013)
You ROCK! 😀Do you understand how and why it works?
Well it looks like it says that for each question in the QuestionID column, return the maximum numerical value related for the associated Response and making the rest blank. In order to do this, Response must be converted to characters. The Group By statement pivots all the other variables not in the statement and groups them based on those variables. Is that right?
Mostly correct. I say "mostly" because you don't actually need to convert the respose to VARCHAR. Dwaine did that just so he could show a blank instead of a NULL (which also suppresses the NULL aggregate warning messages).
If you'd like to do a deeper dive on CrossTabs and Pivots, here are a couple of articles that start you on the ground floor and move you all the way through dynamic CrossTabs.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.