title: Pivot Dynamic Columns, no Aggregation
body: <p>I have questionnaire data in, SQL Server 2008, that I want to transpose to a matrix.<br>
  I saw several posts about the same topic, but I just don't get pivoting.  </p>
  
  <p>Given are following tables:  </p>
  
  <p><code>Question table</code></p>
  
  <p><code>Answer table</code></p>
  
  <p><code>Customer table</code>  </p>
  
  <p><strong>The columns:</strong><br>
  <code>[CustomerID]</code>, <code>[QuestionName_1]</code>, .., <code>[QuestionName_n]</code> &lt;- <strong>dynamic</strong> number of question columns)<br>
  <strong>The data:</strong><br>
  <code>CustomerID</code>, <code>Answer_1</code>, .., <code>Answer_n</code>  </p>
  
  <p>The code to retrieve the columns:  </p>
  
  <pre><code>DECLARE @columns VARCHAR(8000)
  
  SELECT @columns = COALESCE(@columns + ',[' + cast(QuestionName as varchar) + ']',
  '[' + cast(QuestionName as varchar)+ ']')
  FROM Answer A 
  INNER JOIN Question Q ON A.QuestionID = Q.QuestionID
  INNER JOIN Customer C ON A.CustomerID = C.CustomerID
  GROUP BY Q.QuestionName
  
  SET @columns = '[CustomerID],' + @columns
  
  DECLARE @query VARCHAR(8000)
  SET @query = 'Some PIVOT query without aggregation'
  
  EXECUTE(@query)
  </code></pre>
  
  <p>The initial query idea was taken from <a href="http://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/" rel="nofollow" title="pivots with dynamic columns">pivots with dynamic columns</a>.</p>
  
  <p>Can it be done and what would the pivoting query look like?<br>
  ps: I don't want to use ranking with a maximum number of columns. </p>
  
  <p>Regards,</p>
  
  <p>Michel</p>