Joining In Dynamic SQL When Building Columns Headers

Question

I have the following two tables:

Quiz Table:

QuizNo |   Status
  1       Reviewed
  2       Not Reviewed

Quiz Response Table:

QuizNo  | QuestionID | Response
1             11         Yes
2             13         No
2             11         Yes
1             13         No

This is the expected result (11 and 13 being question ID's):

QuizNo  |  Status     |   11   |    13
  1        Reviewed       Yes       No
  2        Not Reviewed   Yes       No

This is my current query, which is not functioning:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(QuestionID) 
                    from Quiz
                    INNER JOIN QuizResponse x ON QuizNo = x.QuizNo
                    group by QuestionID, QuizNo
                    order by QuestionID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select QuestionID
                from QuizResponse
            ) x
            pivot 
            (
                max(QuestionID)
                for QuestionID in (' + @cols + N')
            ) p '

exec sp_executesql @query;

How would you complete/change the query to get the expected result?

TIA!

"*This is my current query, which is not functioning*", what does this mean?, what does it return? — Lamak, Sep 12 '14 at 15:16
Your tables are called "Quiz" and "Quiz Response". Your sample code does not have these tables. Please fix the question. — Gordon Linoff, Sep 12 '14 at 15:17
slightly extended version of your previous question: http://stackoverflow.com/questions/25810330/values-to-column-headers — Tanner, Sep 12 '14 at 15:19
Fixed, and the error is Ambiguous column name 'QuizNo'. But I do not think that is the only thing that is wrong with it to achieve the expected result. — user3010406, Sep 12 '14 at 15:19

score 1 · Accepted Answer · answered Sep 12 '14 at 15:38

You were close, but there's a few things needed before the query could be complete.

The Query in the Stuff function needed to have both QuizNo's specified with the table alias. It also should not group by QuizID, otherwise you will end up with duplicate column definitions (one [11] for each QuizID with a QuestionID 11). So tidied up the group by.

Finally, updated the dynamic SQL to select the columns that aren't being pivoted, and select max(Response) rather than QuestionID as the data for each cell.

See the SQLFiddle example.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(QuestionID) 
                    from Quiz Q
                    INNER JOIN QuizResponse x ON Q.QuizNo = x.QuizNo
                    GROUP BY QuestionID
                    ORDER BY QuestionID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = N'SELECT QuizNo, [Status], ' + @cols + N' from 
             (
                select QR.QuizNo, Q.[Status], QR.QuestionID, QR.Response
                from QuizResponse QR
                  inner join Quiz Q on QR.QuizNo = Q.QuizNo
            ) x
            pivot 
            (
                max(Response)
                for QuestionID in (' + @cols + N')
            ) p '

exec sp_executesql @query;

score 0 · Answer 2 · answered Sep 12 '14 at 15:19

0

I would do this using conditional aggregation:

select q.quizno, q.status,
       max(case when qr.questionid = 11 then qr.response end) as [11],
       max(case when qr.questionid = 13 then qr.response end) as [13]
from quiz q join
     quizreponse qr
     on qr.quizno = q.quizno
group by q.quizno, q.status;

answered Sep 12 '14 at 15:19

Gordon Linoff

1,242,037
58
646
786

is it possible to do in a fashion where it can dynamically read the question ID? Meaning, I have 8000 question ID's... – user3010406 Sep 12 '14 at 15:28
In that case, no, because 8000 exceeds the limits of the number of columns of a table/result set. – Gordon Linoff Sep 12 '14 at 15:36
What if there were just 300 ID's? – user3010406 Sep 12 '14 at 15:39

Joining In Dynamic SQL When Building Columns Headers

2 Answers2