How to use TSQL STUFF in a Pivot?

Question

I am rewriting a TSQL query that uses the STUFF operator to pivot the summed answer values horizontally by question numbers. I've declared a parameter and set it to use the above operator. However, I receive incorrect syntax near @cols when I place the parameter inside the IN portion of the pivot statement.

DECLARE @cols AS NVARCHAR(MAX)

SELECT @cols = STUFF((
            SELECT ',' + QUOTENAME(ColName)
            FROM (
                SELECT DISTINCT CONVERT(VARCHAR(50), QuestionNumber) AS ColName
                FROM Questions
                WHERE StudyID = 23
                ) AS sub
            ORDER BY CONVERT(INT, ColName) ASC
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

WITH cteX
AS (
    SELECT q.StudyID
        ,r.RespID
        ,p.ProductNumber
        ,p.ProductSequence
        ,CONVERT(VARCHAR(50), s.DateAdded, 101) AS StudyDate
        ,CONVERT(VARCHAR(15), CAST((s.DateAdded) AS TIME), 100) AS StudyTime
        ,DATENAME(dw, s.DateAdded) AS [DayOfWeek]
        ,p.[A_Value]
        ,p.B_Value
        ,p.C_Value
        ,p.D_Value
        ,p.E_Value
        ,p.F_Value
        ,q.DependentVarYN
        ,q.VariableAttributeID
        ,v.VarAttributeName
        ,q.QuestionNumber
        ,q.BottomScaleAnchor
        ,q.BottomScaleValue
        ,q.TopScaleAnchor
        ,q.TopScaleValue
    FROM Questions q
    INNER JOIN Answers a ON q.QuestionID = a.QuestionID
    INNER JOIN Respondents r ON a.RespID = r.RespID
    INNER JOIN Products p ON a.StudyID = p.ProductID
    INNER JOIN Studies s ON q.StudyID = s.StudyID
    INNER JOIN VariableAttributesForQuestions v ON q.VariableAttributeID = v.VariableAttributeID
    )
--,
SELECT StudyID
    ,RespID
    ,ProductNumber
    ,ProductSequence
    ,StudyDate
    ,StudyTime
    ,[DayOfWeek]
    ,cteX.[A_Value] AS [A]
    ,cteX.[B_Value] AS B
    ,cteX.[C_Value] AS C
    ,cteX.[D_Value] AS D
    ,cteX.E_Value AS E
    ,cteX.F_Value AS F
    ,DependentVarYN
    ,VariableAttributeID
    ,VarAttributeName
    ,BottomScaleAnchor
    ,BottomScaleValue
    ,TopScaleAnchor
    ,TopScaleValue
FROM cteX
pivot(sum(AnswerValue) FOR cteX.QuestionNumber IN (@cols)) AS piv

How can I optimize the query so that the pivot table parses the AnswerValue sums for each question?

Answer 1

You can't create a dynamic pivot like this. You need to build a T-SQL statement as a string and then execute it.

DECLARE @cols AS NVARCHAR(MAX)

SELECT @cols = STUFF((
            SELECT ',' + QUOTENAME(ColName)
            FROM (
                SELECT DISTINCT CONVERT(VARCHAR(50), QuestionNumber) AS ColName
                FROM Questions
                WHERE StudyID = 23
                ) AS sub
            ORDER BY CONVERT(INT, ColName) ASC
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @DynamicTSQLStatement NVARCHAR(MAX) = N'


WITH cteX
AS (
    SELECT q.StudyID
        ,r.RespID
        ,p.ProductNumber
        ,p.ProductSequence
        ,CONVERT(VARCHAR(50), s.DateAdded, 101) AS StudyDate
        ,CONVERT(VARCHAR(15), CAST((s.DateAdded) AS TIME), 100) AS StudyTime
        ,DATENAME(dw, s.DateAdded) AS [DayOfWeek]
        ,p.[A_Value]
        ,p.B_Value
        ,p.C_Value
        ,p.D_Value
        ,p.E_Value
        ,p.F_Value
        ,q.DependentVarYN
        ,q.VariableAttributeID
        ,v.VarAttributeName
        ,q.QuestionNumber
        ,q.BottomScaleAnchor
        ,q.BottomScaleValue
        ,q.TopScaleAnchor
        ,q.TopScaleValue
    FROM Questions q
    INNER JOIN Answers a ON q.QuestionID = a.QuestionID
    INNER JOIN Respondents r ON a.RespID = r.RespID
    INNER JOIN Products p ON a.StudyID = p.ProductID
    INNER JOIN Studies s ON q.StudyID = s.StudyID
    INNER JOIN VariableAttributesForQuestions v ON q.VariableAttributeID = v.VariableAttributeID
    )
--,
SELECT StudyID
    ,RespID
    ,ProductNumber
    ,ProductSequence
    ,StudyDate
    ,StudyTime
    ,[DayOfWeek]
    ,cteX.[A_Value] AS [A]
    ,cteX.[B_Value] AS B
    ,cteX.[C_Value] AS C
    ,cteX.[D_Value] AS D
    ,cteX.E_Value AS E
    ,cteX.F_Value AS F
    ,DependentVarYN
    ,VariableAttributeID
    ,VarAttributeName
    ,BottomScaleAnchor
    ,BottomScaleValue
    ,TopScaleAnchor
    ,TopScaleValue
FROM cteX
pivot(sum(AnswerValue) FOR cteX.QuestionNumber IN (' + @cols + ')) AS piv';


exec sp_executesql @DynamicTSQLStatement;