SQL Server 2005 dinâmica no número desconhecido de colunas

Eu estou trabalhando com um dataset que se parece com o seguinte.

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 

O nome e o número de atribuições são dynamics, eu preciso obter resultados semelhantes aos seguintes.

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

Agora, idealmente, gostaria de classificar a coluna com base em uma “data de vencimento” que pudesse ser incluída / associada a cada atribuição. O total deve estar no final, se possível (ele pode ser calculado e removido da consulta, se possível).

Eu sei como fazer isso para as 3 tarefas usando o pivô simplesmente nomeando as colunas, ele está tentando fazer isso de uma maneira dinâmica que eu não encontrei uma solução BOM por enquanto. Eu estou tentando fazer isso no SQL Server 2005

EDITAR

Idealmente, gostaria de implementar isso sem usar SQL dynamic, pois isso é contra a política. Se não for possível … então, um exemplo de trabalho com o SQL dynamic funcionará.

Eu sei que você não disse SQL dynamic, mas eu não vejo nenhuma maneira de fazê-lo em SQL direto.

Se você verificar minhas respostas para problemas semelhantes na tabela dinâmica e concatenar colunas e PIVOT no sql 2005

O SQL dynamic não é vulnerável à injeção, e não há uma boa razão para proibi-lo. Outra possibilidade (se os dados estão mudando muito raramente) é fazer a geração de código – em vez de SQL dynamic, o SQL é gerado para um procedimento armazenado em uma base regular.

Para PIVOT esses dados usando o sql dynamic, você pode usar o seguinte código no SQL Server 2005+:

Criar a tabela:

 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) ; 

Pivot Dinâmico:

 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) 

Veja SQL Fiddle com Demo

O resultado é:

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

A única maneira que eu encontrei para fazer isso é usar SQL dynamic e colocar os labels de coluna em uma variável.

você poderia consultar o information_schema para obter os nomes e os tipos de colunas e, em seguida, usar o resultado como uma subconsulta ao criar seu conjunto de resultados. Note que você provavelmente terá que alterar o access do login um pouco.

Este é o mesmo que o PIVOT no sql 2005

Se esses dados são para consumo em um relatório, você poderia usar uma matriz do SSRS. Ele irá gerar colunas dinamicamente a partir do conjunto de resultados. Eu usei isso muitas vezes – funciona muito bem para relatórios dynamics em crosstab.

Aqui está um bom exemplo com sql dynamic. http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

 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 
 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