pasar - pivot sql server

Pivote de SQL Server 2005 sobre el número desconocido de columnas

Question

Estoy trabajando con un conjunto de datos que se parece a lo siguiente.

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

El nombre y el número de asignaciones son dinámicos, necesito obtener resultados similares a los siguientes.

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

Ahora, idealmente, me gustaría ordenar la columna en función de una "fecha de vencimiento" que podría incluirse / asociarse con cada tarea. El total debe estar al final si es posible (se puede calcular y eliminar de la consulta si es posible).

Sé cómo hacerlo para las 3 tareas usando pivot simplemente nombrando las columnas, está tratando de hacerlo de una manera dinámica que todavía no he encontrado una buena solución. Estoy tratando de hacer esto en SQL Server 2005

EDITAR

Idealmente, me gustaría implementar esto SIN usar SQL dinámico, ya que eso va en contra de la política. Si no es posible ... entonces funcionará un ejemplo de trabajo con SQL dinámico.

Answer 1

La única forma que he encontrado para hacer esto es usar SQL dinámico y poner las etiquetas de columna en una variable.

Answer 2

puede consultar information_schema para obtener los nombres y los nombres de columna, luego use el resultado como una subconsulta cuando construya su conjunto de resultados. Tenga en cuenta que es probable que tenga que cambiar el acceso de inicio de sesión un poco.

Answer 3

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

Answer 4

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

Answer 5

Para PIVOT esta información usando sql dinámico puede usar el siguiente código en SQL Server 2005+:

Crear mesa:

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)

Ver SQL Fiddle con demostración

El resultado es:

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

Answer 6

Esto es lo mismo que PIVOT en sql 2005

Si estos datos son para consumo en un informe, podría usar una matriz SSRS. Generará columnas dinámicamente desde el conjunto de resultados. Lo he usado muchas veces: funciona bastante bien para los informes dinámicos de tabla cruzada.

Aquí hay un buen ejemplo con sql dinámico. http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Answer 7

Sé que dijiste que no hay SQL dinámico, pero no veo ninguna forma de hacerlo en SQL directo.

Si revisa mis respuestas a problemas similares en Pivot Table y Concatenate Columns y PIVOT en sql 2005

El SQL dinámico no es vulnerable a la inyección, y no hay una buena razón para prohibirlo. Otra posibilidad (si los datos cambian muy infrecuentemente) es generar código: en lugar de SQL dinámico, el SQL se genera con un procedimiento almacenado de forma regular.