SQL Server 2008 - Dynamic Pivot on one column, group by another, maintain reference to third

Question

I've just started learning SQL and am struggling with dynamically transposing a three column table correctly. I previously hard coded the query, but now need it to be dynamic as the values in my pivot column may change in the future.

Here's my starting point:

questionid | DebriefingQuestionResults | OperationSessionRecordID
------------------------------------------------------------------
32         |        3                  | 8071
34         |        0                  | 8071
36         |        1                  | 8071
32         |        2                  | 8074
34         |        6                  | 8074
36         |        5                  | 8074

And here's what I want to produce:

    OperationSessionRecordID | 32 | 34 | 36
----------------------------------------------
    8071                     | 3  | 0  | 1       
    8074                     | 2  | 6  | 5

There are only three [questionid] values (32, 34, and 36), at the moment but this may change in the future, hence wanting a dynamic query. There are about 12000 [OperationSessionRecordID] values. All columns are of the type int not null.

Based on this answer I've got this so far, but am not sure how to proceed as it throws the error shown below.

USE training_db
--Test to see if table exists, if so drop ready for creation--  
    IF OBJECT_ID('TheatreMan.DebriefingQuestionsResultsPivoted','U') IS NOT NULL
        DROP TABLE TheatreMan.DebriefingQuestionsResultsPivoted
--Declare query and variable names--    
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
--Get Distinct values of the PIVOT column--   
    SET @ColumnName = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.questionid)
    FROM dbo.DebriefingQuestionsResultsTEMP
    FOR XML PATH (''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')
--Prepare the PIVOT query using the dynamic 
    SET @DynamicPivotQuery = 
    'SELECT OperationSessionRecordID, ' + @ColumnName + '
        (select questionid,
            DebriefingQuestionResults
            OperationSessionRecordID
            FROM dbo.DebriefingQuestionsResultsTEMP)
            x
            PIVOT (
            min(DebriefingQuestionResults)
        for questionid in (' + @ColumnName + ')
        )
        AS PIV'
--Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery

When I run this it throws this error, so something's obviously wrong with my @ColumnName variable, but I can't work out what it is.

Msg 4104, Level 16, State 1, Line 9 The multi-part identifier "c.questionid" could not be bound.

Any help would be most appreciated!

SB

So why don't you declare @DynamicPivotQuery variable? – AK47 May 07 '14 at 11:11 — AK47, May 07 '14 at 11:11
@AK47 cheers, just spotted that! Updated to reflect it. – s_boardman May 07 '14 at 11:12 — s_boardman, May 07 '14 at 11:12

score 1 · Accepted Answer · answered May 07 '14 at 11:30

This should work:

declare @collist nvarchar(max)
SET @collist = stuff((select distinct ',' + QUOTENAME(questionid) 
            FROM #t -- your table here
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

declare @q nvarchar(max)
set @q = '
select OperationSessionRecordID, ' + @collist + ' 
from (
    select OperationSessionRecordID, DebriefingQuestionResults, questionid
        from (
        select *
        from #t -- your table here
    ) as x
) as source
pivot (
    sum(DebriefingQuestionResults)
    for questionid in (' + @collist + ')
) as pvt
'

exec (@q)

Works a treat. Thanks very much @dean. :) – s_boardman May 07 '14 at 11:39 — s_boardman, May 07 '14 at 11:39

AK47 · Answer 2 · 2014-05-07T11:20:26.753

try this, I think you just misspelled variable name.

USE training_db
--Test to see if table exists, if so drop ready for creation--  
    IF OBJECT_ID('TheatreMan.DebriefingQuestionsResultsPivoted','U') IS NOT NULL
        DROP TABLE TheatreMan.DebriefingQuestionsResultsPivoted
--Declare query and variable names--    
    DECLARE @    USE training_db
--Test to see if table exists, if so drop ready for creation--  
    IF OBJECT_ID('TheatreMan.DebriefingQuestionsResultsPivoted','U') IS NOT NULL
        DROP TABLE TheatreMan.DebriefingQuestionsResultsPivoted
--Declare query and variable names--    
    DECLARE @QuestionPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
--Get Distinct values of the PIVOT column--   
    SET @ColumnName = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.questionid)
    FROM dbo.DebriefingQuestionsResultsTEMP
    FOR XML PATH (''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')
--Prepare the PIVOT query using the dynamic 
    SET @DynamicPivotQuery = 
    'SELECT OperationSessionRecordID, ' + @ColumnName + '
        (select questionid,
            DebriefingQuestionResults
            OperationSessionRecordID
            FROM dbo.DebriefingQuestionsResultsTEMP
            ORDER BY OperationSessionRecordID ASC
        )x
        PIVOT 
        (
            min(DebriefingQuestionResults)
            for questionid in (' + @ColumnName + ')
        )
        AS PIV'

--Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
--Get Distinct values of the PIVOT column--   
    SET @ColumnName = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.questionid)
    FROM dbo.DebriefingQuestionsResultsTEMP
    FOR XML PATH (''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')
--Prepare the PIVOT query using the dynamic 
    SET @DynamicPivotQuery = 
    'SELECT OperationSessionRecordID, ' + @ColumnName + '
        (select questionid,
            DebriefingQuestionResults
            OperationSessionRecordID
            FROM dbo.DebriefingQuestionsResultsTEMP)
            x
            PIVOT (
            min(DebriefingQuestionResults)
        for questionid in (' + @ColumnName + ')
        )
        AS PIV'
    ORDER BY OperationSessionRecordID ASC
--Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery

Thanks for pointing that out; updated and still not working. See question edit. — s_boardman, May 07 '14 at 11:16

SQL Server 2008 - Dynamic Pivot on one column, group by another, maintain reference to third

2 Answers2