title: Prefix column names when using dynamic Pivot
body: <p>So I am working on a way to report some data from a table that tracks patients response to questions about chest pain when they visit the emergency room. When patients arrive to the ER they are asked a series of questions regarding their chest pain. For each question they are asked to rank the pain from 1 to 10. Below is the data as it is stored in the table.</p>
  
  <pre><code>+-----------+------------+----------+
  | PatientId | QuestionId | PainRank |
  +-----------+------------+----------+
  |     1     |      1     |     5    |
  +-----------+------------+----------+
  |     1     |      2     |     6    |
  +-----------+------------+----------+
  |     1     |      3     |     4    |
  +-----------+------------+----------+
  |     2     |      1     |     1    |
  +-----------+------------+----------+
  |     2     |      2     |     8    |
  +-----------+------------+----------+
  |     2     |      3     |     2    |
  +-----------+------------+----------+
  |     3     |      1     |     5    |
  +-----------+------------+----------+
  |     3     |      2     |     4    |
  +-----------+------------+----------+
  |     3     |      3     |     7    |
  +-----------+------------+----------+
  </code></pre>
  
  <p>I have created a dynamic Pivot of the data which changes the questions to columns as such so that physicians can look at a summary of a group a patients.</p>
  
  <pre><code>+-----------+---+---+---+
  | PatientId | 1 | 2 | 3 |
  +-----------+---+---+---+
  |     1     | 5 | 6 | 4 |
  +-----------+---+---+---+
  |     2     | 1 | 8 | 2 |
  +-----------+---+---+---+
  |     3     | 5 | 4 | 7 |
  +-----------+---+---+---+
  </code></pre>
  
  <p>What I would like to do is prefix the question numbers with the word "Question" so the columns will look like "Question - 1", "Question - 2", "Question - 3". I would like to add the prefix using the SQL query that created the dynamic pivot but since the Pivot is dynamic and am not sure how to add it.</p>
  
  <p>Here is the SQL I used to obtain the dynamic Pivot.</p>
  
  <pre><code>  -- Dynamic Pivot
  DECLARE @PivotQuery AS NVARCHAR(MAX)
  DECLARE @ColumnNameCollection AS NVARCHAR(MAX)
  
  -- Get column list. A DISTINCT list of questions
  SELECT @ColumnNameCollection= ISNULL(@ColumnNameCollection + ',','') 
         + QUOTENAME(QuestionId)
  FROM (SELECT DISTINCT QuestionId FROM PatientChestPain) AS Questions
  
  -- Build PIVOT query using the list of questions
  SET @PivotQuery = 
    N'SELECT PatientId,  ' + @ColumnNameCollection + '
      FROM PatientChestPain
      PIVOT(MAX(PainRank) 
            FOR QuestionId IN (' + @ColumnNameCollection + ')) AS PivotTableResult'
  --Execute the PIVOT query
  EXEC sp_executesql @PivotQuery
  </code></pre>
  
  <p>I do realize I could just edit the final report and add "Question - ", however I am trying to generate the report automatically and I can only manipulate the SQL.</p>
  
  <p>NOTE: I used the <a href="http://www.tablesgenerator.com/text_tables#" rel="nofollow">following website</a> to generate the ascii tables. Very nice interface and easy to use. I tried creating a SQL Fiddle however it appears as though it is not working with SQL Server at the moment.</p>