SQL Server PIVOT help.

Question

Source table:

Create Table ExamAnswers
{
   StudentID varchar(12),
   QuestionID int,
   Answer char(1)
}

and this will be filled with

Bob 1 a
Bob 2 c
...
Bob 100 b
Chris 1 c
Chris 2 d
...
Chris 100 null

etc, for about 500 students.

Chris did not finish the exam, but the 100th question is stored as null, so it is guaranteed that each student has exactly 100 rows, but the actual answer is null or character.

If it makes any difference, answers are in {a,b,c,d,e,f}

This setup works great for the actual exam application, and marking it is trivial.

Now I have a reporting requirement, that for audit purposes, I need to produce a table that looks like this:

ID    1 2 ... 100 
Bob   a c ... b
Chris c d ....null

So I spent half a day reading about the PIVOT function, and I just don't get it.

That has to be the most impenetrable documentation I've ever read.

For one thing, it requires and aggregate function -- What the heck am I supposed to be aggregating here?

I figure that this is just about the simplest use of the PIVOT function that there could be, and I can't find a decent example anywhere. HELP!

Answer 1

Look at this article: Using PIVOT and UNPIVOT

Quote:

The following is annotated syntax for PIVOT.

SELECT <non-pivoted column> ,

    [first pivoted column] AS <column name> ,

    [second pivoted column] AS <column name> ,

    ...

    [last pivoted column] AS <column name>

FROM 

    ( <SELECT query that produces the data> ) 

    AS <alias for the source query>

PIVOT 

( 

    <aggregation function>( <column being aggregated> )

FOR 

[<column that contains the values that will become column headers>] 

    IN ( [first pivoted column] , [second pivoted column] ,

    ... [last pivoted column] )

) AS <alias for the pivot table>

<optional ORDER BY clause>

As you can see there must be aggregation function(column being aggregated). So Answer column in your table has to be integer (decimal, etc.), not char(1).

EDIT: MIN() and MAX() work for char() data type.

Your table can be like this:

Create Table ExamAnswers
(
   StudentID varchar(12) NOT NULL,
   QuestionID int NOT NULL,
   Answer int
)

And SELECT statement with PIVOT, that give the result you need will be:

SELECT StudentID, [1] as Q1,  [2] as Q2, [3] as Q3, [4] as Q4, [5] as Q5 
FROM 
(
SELECT StudentID, QuestionID, Answer
FROM dbo.ExamAnswers
) AS piv
PIVOT
(
AVG(Answer)
 FOR QuestionID IN ([1], [2], [3], [4], [5])
) AS chld

Answer 2

OK solved it. MAX or MIN will work on a char field. So:

Create Table ExamAnswers
{
   StudentID varchar(12),
   QuestionID int,
   Answer char(1)
}

As originally created

And then

SELECT StudentID, [1] as Q1,  [2] as Q2, [3] as Q3, [4] as Q4, [5] as Q5 
FROM 
(
SELECT StudentID, QuestionID, Answer
FROM dbo.ExamAnswers
) AS piv
PIVOT
(
MAX(Answer)
 FOR QuestionID IN ([1], [2], [3], [4], [5])
) AS chld

The confusion lays in the selection of an Aggregate where there is no logical reason to aggregate anything. I should mention that StudentID and QuestionID form a compound key, so there is only one possible Answer value for any given SID and QID pair.