Dynamic Pivot with SQL in SQL Server

 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:

1. Aggregate the Data:

   • Use the base query to gather the employee data, question texts, and their corresponding answers.

2. Prepare the Pivot:

• Use the PIVOT operator 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