SQL Server 2005 Pivot on Unknown Number of Columns

Question

I am working with a set of data that looks something like the following.

StudentName  | AssignmentName |  Grade
---------------------------------------
StudentA     | Assignment 1   | 100
StudentA     | Assignment 2   | 80
StudentA     | Total          | 180
StudentB     | Assignment 1   | 100
StudentB     | Assignment 2   | 80
StudentB     | Assignment 3   | 100
StudentB     | Total          | 280

The name and number of assignments are dynamic, I need to get results simlilar to the following.

Student      | Assignment 1  | Assignment 2  | Assignment 3  | Total
--------------------------------------------------------------------
Student A    | 100           | 80            | null          | 180
Student B    | 100           | 80            | 100           | 280

Now ideally I would like to sort the column based on a "due date" that could be included/associated with each assignment. The total should be at the end if possible (It can be calculated and removed from the query if possible.)

I know how to do it for the 3 assignments using pivot with simply naming the columns, it is trying to do it in a dynamic fashion that I haven't found a GOOD solution for yet. I am trying to do this on SQL Server 2005

EDIT

Ideally I would like to implement this WITHOUT using Dynamic SQL, as that is against the policy. If it isn't possible...then a working example with Dynamic SQL will work.

Answer 1

To PIVOT this data using dynamic sql you can use the following code in SQL Server 2005+:

Create Table:

CREATE TABLE yourtable
    ([StudentName] varchar(8), [AssignmentName] varchar(12), [Grade] int)
;

INSERT INTO yourtable
    ([StudentName], [AssignmentName], [Grade])
VALUES
    ('StudentA', 'Assignment 1', 100),
    ('StudentA', 'Assignment 2', 80),
    ('StudentA', 'Total', 180),
    ('StudentB', 'Assignment 1', 100),
    ('StudentB', 'Assignment 2', 80),
    ('StudentB', 'Assignment 3', 100),
    ('StudentB', 'Total', 280)
;

Dynamic PIVOT:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(AssignmentName) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT StudentName, ' + @cols + ' from 
             (
                select StudentName, AssignmentName, grade
                from yourtable
            ) x
            pivot 
            (
                min(grade)
                for assignmentname in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

The result is:

| STUDENTNAME | ASSIGNMENT 1 | ASSIGNMENT 2 | ASSIGNMENT 3 | TOTAL |
--------------------------------------------------------------------
|    StudentA |          100 |           80 |       (null) |   180 |
|    StudentB |          100 |           80 |          100 |   280 |