CODEWITHSUNDEEP
sqlsql-serverpivot
Mitchel Sellers
Mitchel Sellers

Reputation: 63126

SQL Server 2005 Pivot on Unknown Number of Columns

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.

Upvotes: 23

Views: 54130

Answers (7)

Taryn
Taryn

Reputation: 247720

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 |

Upvotes: 12

Chirag Patel
Chirag Patel

Reputation: 1

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

Upvotes: -1

Prasad
Prasad

Reputation: 1

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

Upvotes: -3

Booji Boy
Booji Boy

Reputation: 4582

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

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89671

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.

Upvotes: 12

tsilb
tsilb

Reputation: 8037

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.

Upvotes: 0

BoltBait
BoltBait

Reputation: 11489

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

Upvotes: 1

Related Questions