Prefix column names when using dynamic Pivot

Question

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.

+-----------+------------+----------+
| 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    |
+-----------+------------+----------+

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.

+-----------+---+---+---+
| PatientId | 1 | 2 | 3 |
+-----------+---+---+---+
|     1     | 5 | 6 | 4 |
+-----------+---+---+---+
|     2     | 1 | 8 | 2 |
+-----------+---+---+---+
|     3     | 5 | 4 | 7 |
+-----------+---+---+---+

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.

Here is the SQL I used to obtain the dynamic Pivot.

  -- 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

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.

NOTE: I used the following website 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.

Answer 1

I found an easy way. Just create a header string where you create an alias for each column

DEMO

  -- Dynamic Pivot
DECLARE @PivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnNameCollection AS NVARCHAR(MAX)
DECLARE @HeaderNameCollection 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

-- Get header list. A DISTINCT list of Questions
SELECT @HeaderNameCollection= ISNULL(@HeaderNameCollection + ',','') 
       + QUOTENAME(QuestionId) + ' as Q' + CAST(QuestionId AS VARCHAR(16))
FROM (SELECT DISTINCT QuestionId FROM PatientChestPain) AS Questions

SELECT @ColumnNameCollection;
SELECT @HeaderNameCollection;

-- Build PIVOT query using the list of Questions
SET @PivotQuery = 
  N'SELECT PatientId,  ' + @HeaderNameCollection + '
    FROM PatientChestPain
    PIVOT(MAX(PainRank) 
          FOR QuestionId IN (' + @ColumnNameCollection + ')) AS PivotTableResult'
--Execute the PIVOT query

SELECT @PivotQuery;
EXEC sp_executesql @PivotQuery

OUTPUT