In our example we have a Dynamic Survey form in the database which we would use to take survey of employees across an agency. Now when we have collected the survey we need to generate a report in pivot format which would give u one row per employee and in the columns we would get answers to each question. Interesting!
Lets look at the options available in SQL Server.
To pivot your query and transform it into a format where each row represents an employee and the columns contain answers for each question, you can use a PIVOT operation.
Steps:
Aggregate the Data:
- Use the base query to gather the employee data, question texts, and their corresponding answers.
Prepare the Pivot:
- Use the
PIVOToperator to dynamically transform rows (questions and answers) into columns for each question.
If the questions are dynamic and you don't know them in advance, you need to dynamically generate the column list using dynamic SQL.
Handling Dynamic Questions:
If the list of questions is not fixed and changes frequently, you can use dynamic SQL to generate the column list automatically.
DECLARE @Columns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
-- Step 1: Generate the Column List Using FOR XML PATH
SELECT @Columns = STUFF((
SELECT DISTINCT ',' + QUOTENAME(QUESTION)
FROM QUIZ_QUESTIONS
WHERE QUESTIONID IN (
SELECT DISTINCT QUESTIONID
FROM QUIZ_ATTEMPT_RESULT
WHERE QUIZASSIGNID IN (SELECT QUIZASSIGNID FROM QUIZ_ASSIGNMENT WHERE QUIZID = 1)
)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
-- Step 2: Construct the Dynamic Pivot Query
SET @SQL = '
WITH BaseData AS (
SELECT
EMPLOYEES.EMPLOYEEID,
EMPLOYEES.FIRSTNAME + '' '' + EMPLOYEES.LASTNAME AS EmployeeName,
QUIZ_QUESTIONS.QUESTION AS QuestionText,
QUIZ_QUESTION_ANSWERS.ANSWER AS Answer
FROM [dbo].[QUIZ_ASSIGNMENT]
INNER JOIN EMPLOYEES
ON EMPLOYEES.EMPLOYEEID = [QUIZ_ASSIGNMENT].EMPLOYEEID
INNER JOIN [dbo].[QUIZ_ATTEMPT_RESULT]
ON QUIZ_ATTEMPT_RESULT.QUIZASSIGNID = QUIZ_ASSIGNMENT.QUIZASSIGNID
INNER JOIN [dbo].[QUIZ_QUESTION_ANSWERS]
ON QUIZ_QUESTION_ANSWERS.ANSWERID = QUIZ_ATTEMPT_RESULT.ANSWERID
INNER JOIN [dbo].[QUIZ_QUESTIONS]
ON QUIZ_QUESTIONS.QUESTIONID = QUIZ_ATTEMPT_RESULT.QUESTIONID
WHERE QUIZ_ASSIGNMENT.QUIZID = 1
)
SELECT *
FROM (
SELECT
EmployeeName,
QuestionText,
Answer
FROM BaseData
) AS SourceTable
PIVOT (
MAX(Answer)
FOR QuestionText IN (' + @Columns + ')
) AS PivotTable;
';
-- Step 3: Execute the Dynamic SQL
EXEC sp_executesql @SQL;
Sample Input Data:
| Name | Question | Answer |
|---|---|---|
| John Smith | What is your name? | John |
| John Smith | What is your age? | 30 |
| Jane Doe | What is your name? | Jane |
| Jane Doe | What is your age? | 25 |
Sample Output Data:
| Name | What is your name? | What is your age? |
|---|---|---|
| John Smith | John | 30 |
| Jane Doe | Jane | 25 |
