Prefix column names when using dynamic Pivot
<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>