none
CONSULTA USANDO PIVOT RRS feed

  • Pregunta

  • Hola

    Solicito amablemente su colaboración con la siguiente duda

    Se tiene la siguiente tabla:

    CREATE TABLE NOTAS (
    ID_NOTA INT NOT NULL IDENTITY, 
    CURSO VARCHAR(50),
    MATERIA VARCHAR(50),
    ESTUDIANTE VARCHAR(50), 
    NOTA DECIMAL(18,1), 
    PERIODO VARCHAR(50),
    REGISTRA VARCHAR(50), 
    PRIMARY KEY (ID_NOTA)
    );

    Con los siguientes datos:

    INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','MATEMATIAS','111','4.0','1','DOCENTE') INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','MATEMATIAS','111','5.0','1','ESTUDIANTE') INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','INGLES','111','3.0','1','DOCENTE') INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','INGLES','111','2.0','1','ESTUDIANTE') INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','MATEMATICAS','222','1.0','1','DOCENTE') INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','MATEMATICAS','222','2.0','1','ESTUDIANTE') INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','INGLES','222','3.0','1','DOCENTE') INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','INGLES','222','4.0','1','ESTUDIANTE')

    INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','MATEMATIAS','111','3.0','2','DOCENTE')
    INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','MATEMATIAS','111','4.0','2','ESTUDIANTE')
    INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','INGLES','111','5.0','2','DOCENTE')
    INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','INGLES','111','3.0','2','ESTUDIANTE')
    INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','MATEMATICAS','222','2.0','2','DOCENTE')
    INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','MATEMATICAS','222','4.0','2','ESTUDIANTE')
    INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','INGLES','222','5.0','2','DOCENTE')
    INSERT INTO NOTAS (CURSO,MATERIA,ESTUDIANTE,NOTA,PERIODO,REGISTRA)VALUES('101','INGLES','222','2.0','2','ESTUDIANTE')

    Con la siguiente consulta se obtiene el campo NOTA_DEFINITIVA

    select ESTUDIANTE,materia,periodo,nota,registra,                               
    case                                                                                                    
    when registra = 'ESTUDIANTE' then (0.3 / (SELECT COUNT (*) FROM NOTAS B WHERE B.REGISTRA='ESTUDIANTE' 
    AND B.ESTUDIANTE=A.ESTUDIANTE AND A.MATERIA=B.MATERIA AND A.PERIODO=B.PERIODO) * nota)                                                          
    when registra = 'DOCENTE' then (0.7 / (SELECT COUNT (*) FROM NOTAS B WHERE B.REGISTRA='DOCENTE' 
    AND B.ESTUDIANTE=A.ESTUDIANTE AND A.MATERIA=B.MATERIA AND A.PERIODO=B.PERIODO)* nota)   
    END NOTA_DEFINITIVA                                                                                       
    from notas A                                                                                            
    where  CURSO='101' 
    group by ESTUDIANTE,materia,periodo,nota,registra 

    Obteniendo el siguiente resultado:

    La pregunta sería como hacer para ordenar por medio de una Pivot o con el método más correcto para obtener una salida de esta manera:

    -Ordenar por Estudiante y por periodo

    -Crear columnas por cada materia

    -Traer una NOTA_FINAL a la celda correspondiente en PERIODO-MATERIA

     NOTA_FINAL = (NOTA_DEFINITIVA docente + NOTA_DEFINITIVA estudiante) por materia y periodo

    Desde ya mil gracias por la ayuda que me puedan brindar


    Respuesta de foro Microsoft


    • Editado yulfredy domingo, 19 de septiembre de 2021 4:15
    domingo, 19 de septiembre de 2021 4:13

Respuestas

  • Hola yulfredy:

    En la data tienes puesto una materia MATEMATIAS y en otra MATEMATICAS

    La manera más simple de hacerlo es con un pivot estático.

    Para esto vamos a utilizar un cte correlativo.

    ;WITH SOURCE AS (
    Select ESTUDIANTE
    	 , materia
    	 , periodo
    	 , nota
    	 , registra
    	 , Case
    		   When registra = 'ESTUDIANTE' Then 0.3 / (
    													 Select Count(*)
    															From NOTAS As B
    															Where B.REGISTRA = 'ESTUDIANTE' And B.ESTUDIANTE = A.ESTUDIANTE And A.MATERIA = B.MATERIA And A.PERIODO = B.PERIODO
    												   ) * nota
    		   When registra = 'DOCENTE' Then 0.7 / (
    												  Select Count(*)
    														 From NOTAS As B
    														 Where B.REGISTRA = 'DOCENTE' And B.ESTUDIANTE = A.ESTUDIANTE And A.MATERIA = B.MATERIA And A.PERIODO = B.PERIODO
    												) * nota
    	   End As NOTA_DEFINITIVA
    	   From notas As A
    	   Where CURSO = '101'
    	   Group By ESTUDIANTE
    			  , materia
    			  , periodo
    			  , nota
    			  , registra
    ), dataGroupped As (
    Select ESTUDIANTE, MATERIA, PERIODO, SUM(Nota_Definitiva) as notaFinal 
    from source
    Group by ESTUDIANTE, MATERIA, PERIODO
    )
    Select * 
    from dataGroupped
    pivot (max(notaFinal) for Materia in ([INGLES],[MATEMATICAS]))PVT 
    ORDER BY ESTUDIANTE

    En el primer conjunto llamado source, va la consulta que has facilitado.

    En el segundo conjunto dataGroupped, recogemos las columnas Estudiante, Materia, Periodo y hacemos una suma de la nota definitiva como notaFinal

    Ahora solo nos resta pivotar, por la nota final para cada una de las materias. Aquí es donde es diferente el pivot estático del dinámico, ya que las materias, las tienes que mencionar explicitamente en la consulta.

    El pivot estático es mucho más simple de realizar y de alterar si aparecen nuevos requisitos en la consulta.

    Cte correlativo

    https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-2-correlativos/

    Pivot 

    https://javifer2.wordpress.com/2019/10/16/pivot-simple-como-hacer-y-entenderlo-paso-por-paso/

    • Marcado como respuesta yulfredy domingo, 19 de septiembre de 2021 11:24
    domingo, 19 de septiembre de 2021 6:07
  • Hola yulfredy:

    El pivot dinámico, es más complicado, aunque después de haberlo visto la consulta para el estático, el cambio es pequeño.

    Declare @columns nvarchar(max);
    SELECT @columns= STUFF(
     (
     SELECT
       ',' + QUOTENAME(LTRIM(MATERIA))
     FROM
       (SELECT DISTINCT MATERIA
        FROM Notas
       ) AS T
     ORDER BY
     MATERIA
     FOR XML PATH('')
     ), 1, 1, '');
     DECLARE @sqlQuery NVARCHAR (MAX);
     SET @sqlQuery = N'
    ;WITH SOURCE AS (
    Select ESTUDIANTE
    	 , materia
    	 , periodo
    	 , nota
    	 , registra
    	 , Case
    		   When registra = ''ESTUDIANTE'' Then 0.3 / (
    													 Select Count(*)
    															From NOTAS As B
    															Where B.REGISTRA = ''ESTUDIANTE'' And B.ESTUDIANTE = A.ESTUDIANTE And A.MATERIA = B.MATERIA And A.PERIODO = B.PERIODO
    												   ) * nota
    		   When registra = ''DOCENTE'' Then 0.7 / (
    												  Select Count(*)
    														 From NOTAS As B
    														 Where B.REGISTRA = ''DOCENTE'' And B.ESTUDIANTE = A.ESTUDIANTE And A.MATERIA = B.MATERIA And A.PERIODO = B.PERIODO
    												) * nota
    	   End As NOTA_DEFINITIVA
    	   From notas As A
    	   Where CURSO = ''101''
    	   Group By ESTUDIANTE
    			  , materia
    			  , periodo
    			  , nota
    			  , registra
    ), dataGroupped As (
    Select ESTUDIANTE, MATERIA, PERIODO, SUM(Nota_Definitiva) as notaFinal 
    from source
    Group by ESTUDIANTE, MATERIA, PERIODO
    )
    Select * 
    from dataGroupped
    pivot (max(notaFinal) for Materia in ('+@columns+N'))PVT 
    ORDER BY ESTUDIANTE';
    
    Exec sp_executeSql @sqlQuery
    
    
    

    Solo tienes que conseguir almacenar las columnas en una variable, para concatenarlas a la query.

    Pivot dinámico

    https://javifer2.wordpress.com/2019/11/14/pivot-dinamico-como-hacerlo-y-entenderlo/

    • Marcado como respuesta yulfredy domingo, 19 de septiembre de 2021 11:25
    domingo, 19 de septiembre de 2021 6:15

Todas las respuestas

  • Hola yulfredy:

    En la data tienes puesto una materia MATEMATIAS y en otra MATEMATICAS

    La manera más simple de hacerlo es con un pivot estático.

    Para esto vamos a utilizar un cte correlativo.

    ;WITH SOURCE AS (
    Select ESTUDIANTE
    	 , materia
    	 , periodo
    	 , nota
    	 , registra
    	 , Case
    		   When registra = 'ESTUDIANTE' Then 0.3 / (
    													 Select Count(*)
    															From NOTAS As B
    															Where B.REGISTRA = 'ESTUDIANTE' And B.ESTUDIANTE = A.ESTUDIANTE And A.MATERIA = B.MATERIA And A.PERIODO = B.PERIODO
    												   ) * nota
    		   When registra = 'DOCENTE' Then 0.7 / (
    												  Select Count(*)
    														 From NOTAS As B
    														 Where B.REGISTRA = 'DOCENTE' And B.ESTUDIANTE = A.ESTUDIANTE And A.MATERIA = B.MATERIA And A.PERIODO = B.PERIODO
    												) * nota
    	   End As NOTA_DEFINITIVA
    	   From notas As A
    	   Where CURSO = '101'
    	   Group By ESTUDIANTE
    			  , materia
    			  , periodo
    			  , nota
    			  , registra
    ), dataGroupped As (
    Select ESTUDIANTE, MATERIA, PERIODO, SUM(Nota_Definitiva) as notaFinal 
    from source
    Group by ESTUDIANTE, MATERIA, PERIODO
    )
    Select * 
    from dataGroupped
    pivot (max(notaFinal) for Materia in ([INGLES],[MATEMATICAS]))PVT 
    ORDER BY ESTUDIANTE

    En el primer conjunto llamado source, va la consulta que has facilitado.

    En el segundo conjunto dataGroupped, recogemos las columnas Estudiante, Materia, Periodo y hacemos una suma de la nota definitiva como notaFinal

    Ahora solo nos resta pivotar, por la nota final para cada una de las materias. Aquí es donde es diferente el pivot estático del dinámico, ya que las materias, las tienes que mencionar explicitamente en la consulta.

    El pivot estático es mucho más simple de realizar y de alterar si aparecen nuevos requisitos en la consulta.

    Cte correlativo

    https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-2-correlativos/

    Pivot 

    https://javifer2.wordpress.com/2019/10/16/pivot-simple-como-hacer-y-entenderlo-paso-por-paso/

    • Marcado como respuesta yulfredy domingo, 19 de septiembre de 2021 11:24
    domingo, 19 de septiembre de 2021 6:07
  • Hola yulfredy:

    El pivot dinámico, es más complicado, aunque después de haberlo visto la consulta para el estático, el cambio es pequeño.

    Declare @columns nvarchar(max);
    SELECT @columns= STUFF(
     (
     SELECT
       ',' + QUOTENAME(LTRIM(MATERIA))
     FROM
       (SELECT DISTINCT MATERIA
        FROM Notas
       ) AS T
     ORDER BY
     MATERIA
     FOR XML PATH('')
     ), 1, 1, '');
     DECLARE @sqlQuery NVARCHAR (MAX);
     SET @sqlQuery = N'
    ;WITH SOURCE AS (
    Select ESTUDIANTE
    	 , materia
    	 , periodo
    	 , nota
    	 , registra
    	 , Case
    		   When registra = ''ESTUDIANTE'' Then 0.3 / (
    													 Select Count(*)
    															From NOTAS As B
    															Where B.REGISTRA = ''ESTUDIANTE'' And B.ESTUDIANTE = A.ESTUDIANTE And A.MATERIA = B.MATERIA And A.PERIODO = B.PERIODO
    												   ) * nota
    		   When registra = ''DOCENTE'' Then 0.7 / (
    												  Select Count(*)
    														 From NOTAS As B
    														 Where B.REGISTRA = ''DOCENTE'' And B.ESTUDIANTE = A.ESTUDIANTE And A.MATERIA = B.MATERIA And A.PERIODO = B.PERIODO
    												) * nota
    	   End As NOTA_DEFINITIVA
    	   From notas As A
    	   Where CURSO = ''101''
    	   Group By ESTUDIANTE
    			  , materia
    			  , periodo
    			  , nota
    			  , registra
    ), dataGroupped As (
    Select ESTUDIANTE, MATERIA, PERIODO, SUM(Nota_Definitiva) as notaFinal 
    from source
    Group by ESTUDIANTE, MATERIA, PERIODO
    )
    Select * 
    from dataGroupped
    pivot (max(notaFinal) for Materia in ('+@columns+N'))PVT 
    ORDER BY ESTUDIANTE';
    
    Exec sp_executeSql @sqlQuery
    
    
    

    Solo tienes que conseguir almacenar las columnas en una variable, para concatenarlas a la query.

    Pivot dinámico

    https://javifer2.wordpress.com/2019/11/14/pivot-dinamico-como-hacerlo-y-entenderlo/

    • Marcado como respuesta yulfredy domingo, 19 de septiembre de 2021 11:25
    domingo, 19 de septiembre de 2021 6:15
  • Hola Javi

    Mil gracias por la ayuda

    Funciona y es exactamente lo que se requiere, solo que hay una duda que me surge si me lo permites:

    Veo que la consulta tarda unos 10 segundos Aprox en ejecutar ¿Crees que hay una manera más eficiente o más óptima de conseguir hacer esto mismo pero en menos tiempo?

    Mil gracias


    Respuesta de foro Microsoft




    • Editado yulfredy domingo, 19 de septiembre de 2021 13:03
    domingo, 19 de septiembre de 2021 11:34
  • Hola yulfredy:

    Prueba esto:

    ;WITH SOURCE AS (
    Select ESTUDIANTE
    	 , materia
    	 , periodo
    	 , nota
    	 , registra
    	 , 0.3 / (COUNT(*)) * nota
    		 As NOTA_DEFINITIVA
    	   From notas As A
    	   Where CURSO = '101'
    	   aND REGISTRA = 'ESTUDIANTE' 
    	   Group By ESTUDIANTE
    			  , materia
    			  , periodo
    			  , nota
    			  , registra
    UNION ALL
    
    Select ESTUDIANTE
    	 , materia
    	 , periodo
    	 , nota
    	 , registra
    	 ,  0.7 / Count(*) * nota
    	    As NOTA_DEFINITIVA
    	   From notas As A
    	   Where CURSO = '101'
    	   AND REGISTRA = 'DOCENTE'
    	   Group By ESTUDIANTE
    			  , materia
    			  , periodo
    			  , nota
    			  , registra
    ), dataGroupped As (
    Select ESTUDIANTE, MATERIA, PERIODO, SUM(Nota_Definitiva) as notaFinal 
    from source
    Group by ESTUDIANTE, MATERIA, PERIODO
    )
    Select * 
    from dataGroupped
    pivot (max(notaFinal) for Materia in ([INGLES],[MATEMATICAS]))PVT 
    ORDER BY ESTUDIANTE
    
    

    O esta otra forma, sacando los count a conjuntos diferentes. 

    Presentarán planes de ejecución diferentes, y pueden mejorar el resultado.

    ;WITH C1 AS (
    	SELECT COUNT(*) AS NUM
    	  From notas As A
    	   Where CURSO = '101'
    	   AND REGISTRA = 'ESTUDIANTE' 
    
    ),
    C2 AS (
    	SELECT COUNT(*) AS NUM
    	  From notas As A
    	   Where CURSO = '101'
    	   AND REGISTRA = 'ESTUDIANTE' 
    )
    , SOURCE AS (
    Select ESTUDIANTE
    	 , materia
    	 , periodo
    	 , nota
    	 , registra
    	 , 0.3 / (C1.NUM) * nota
    		 As NOTA_DEFINITIVA
    	   From notas As A CROSS JOIN C1 
    	   Where CURSO = '101'
    	   AND REGISTRA = 'ESTUDIANTE' 
    	   Group By ESTUDIANTE
    			  , materia
    			  , periodo
    			  , nota
    			  , registra
    			  , C1.NUM
    UNION ALL
    
    Select ESTUDIANTE
    	 , materia
    	 , periodo
    	 , nota
    	 , registra
    	 ,  0.7 / C2.NUM * nota
    	    As NOTA_DEFINITIVA
    	   From notas As A CROSS JOIN C2 
    	   Where CURSO = '101'
    	   AND REGISTRA = 'DOCENTE'
    	   Group By ESTUDIANTE
    			  , materia
    			  , periodo
    			  , nota
    			  , registra
    			  , C2.NUM
    ), dataGroupped As (
    Select ESTUDIANTE, MATERIA, PERIODO, SUM(Nota_Definitiva) as notaFinal 
    from source
    Group by ESTUDIANTE, MATERIA, PERIODO
    )
    Select * 
    from dataGroupped
    pivot (max(notaFinal) for Materia in ([INGLES],[MATEMATICAS]))PVT 
    ORDER BY ESTUDIANTE
    
    

    lunes, 20 de septiembre de 2021 4:56