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.

Kill

Answer 1

I know you said no dynamic SQL, but I don't see any way to do it in straight SQL.

If you check out my answers to similar problems at Pivot Table and Concatenate Columns and PIVOT in sql 2005

The dynamic SQL there is not vulnerable to injection, and there is no good reason to prohibit it. Another possibility (if the data is changing very infrequently) is to do code-generation - instead of dynamic SQL, the SQL is generated to a stored procedure on a regular basis.

Counterproductive

Thanks for the reference link and example! I'll just have to go about it that way, dynamic SQL just seems so DIRTY but at times necessary – Kill

Cade, I just wanted to say thanks again! i have this working like clockwork now on my side.... – Kill

No problem. Dynamic SQL is a great tool for doing more work on the server without burdening the client, especially in abstraction layer SPs where many different access modalities might be used - COM, .NET, other SPs, etc. Like anything it should only be used appropriately. – Counterproductive

I'm curious as to how you say the dynamic SQL is not prone to SQL injection. I have a question regarding a dynamic pivot built up in a similar way. This shows how SQL injection attacks can occure with a dynamic PIVOT. #1439903 – Damask

Looking at the code in the other two answers, there are cases where they could be vulnerable to injection due to code/type data in the table. A big con to dynamic SQL is always injection - but there are cases where dynamic SQL generated from the schema is very safe. My main point is that the procedure is not accepting external input. Even those examples could be made safe with QUOTENAME. – Counterproductive

Answer 2

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 |

Essy

What if i would have to replace null with zero ? – Muscatel

@Muscatel see my answer here – Essy

Answer 3

The only way I've found to do this is to use dynamic SQL and put the column labels into a variable.

Brendabrendan

Answer 4

you could query information_schema to get the column names and types, then use the result as a subquery when you build your result set. Note you'll likely have to change the login's access a bit.

Frontispiece

Care to post up an example? The only way I have found ws to build the list using dynamic SQL, and I'd rather not – Kill

I'm not sure you'll be able to get away from dynamic SQL because the PIVOT's IN clause must have hard coded values--no subselect is allowed there. Oh, how I wish this wasn't true! – Brendabrendan

Answer 5

This is the same as PIVOT in sql 2005

If this data is for consumption in a report you could use a SSRS matrix. It will generate columns dynamically from result set. I've used it many times - it works quite well for dynamic crosstab reports.

Here's a good example w/ dynamic sql. http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Sophia

Answer 6

SELECT TrnType
INTO #Temp1
FROM
(
    SELECT '[' + CAST(TransactionType AS VARCHAR(4)) + ']' AS TrnType FROM tblPaymentTransactionTypes
) AS tbl1

SELECT * FROM #Temp1

SELECT * FROM
(
    SELECT FirstName + ' ' + LastName AS Patient, TransactionType, ISNULL(PostedAmount, 0) AS PostedAmount
    FROM tblPaymentTransactions
            INNER JOIN emr_PatientDetails ON tblPaymentTransactions.PracticeID = emr_PatientDetails.PracticeId
            INNER JOIN tblPaymentTransactionDetails ON emr_PatientDetails.PatientId = tblPaymentTransactionDetails.PatientID
                        AND tblPaymentTransactions.TransactionID = tblPaymentTransactionDetails.TransactionID
    WHERE emr_PatientDetails.PracticeID = 152
) tbl
PIVOT (SUM(PostedAmount) FOR [TransactionType] IN (SELECT * FROM #Temp1)
) AS tbl4

Antitrades

this answer either needs significant signposting to go with the code, or translation into the question's stated domain. tblPaymentTransactionTypes is clearly pasted from unrelated code. – Selfacting

Answer 7

select studentname,[Assign1],[Assign2],[Assign3],[Total] 
from 
(
 select studentname, assignname, grade from student
)s
pivot(sum(Grade) for assignname IN([Assign1],[Assign2],[Assign3],[Total])) as pvt

Supposal

Please add an explaination to your answer – Alcaic