Dynamic SQL Pivot with no Aggregate
Imagine a Survey database. The layout of the database is meant to be reusable, DRY-conforming and fast. Meaning it's not very user-friendly. A data dump would be a PITA for any user to interpret. A database diagram is below. One thing that I could not find out how to do online was how to write a PIVOT query without using an aggregate function. The reason is that you cannot. You have to aggregate the data. So the trick is to write your query with only one column of results being pivoted -- making the aggregate meaningless. Another issue I have with PIVOTs is that you need to know something about your DataSet in order to pivot it. That is lame. So the following TSQL query will get a list of all users, their answers to the survey & the questions as columns. It does this by building the PIVOT statement in SQL and then executing it. /***************************************** * * WARNING * If you receive the following error: * Msg 102, Level 15, State 1, Line 4 Incorrect