none
Duda con Pivot + Sumatorio RRS feed

  • Pregunta

  • Buenos días a todos,

    Estoy intentando hacer una query pero no se por donde empezar, os explico:

    Por un lado tengo la tabla Reportes Y por otro lado la tabla Desglose, cada registro de Desglose pertenece a un Reporte y un Reporte tiene 2 lineas de desglose.

    Lo que necesito es una query que me de pivotado la linea uno y la linea 2 de cada Reporte y luego por ultimo en un 3º pivote la suma de los valores de la linea 1 y 2.

    DECLARE @Reporte table (
    ID INT NOT NULL,
    Nombre varchar(50) NOT NULL,
    Fecha Datetime NOT NULL,
    PRIMARY KEY (ID)
    );
    
    INSERT INTO @Reporte
    	(ID, Nombre, Fecha)
    VALUES
    	(1, 'Obra A', '01/01/2017'),
    	(2, 'Obra A', '01/02/2017'),
    	(3, 'Obra A', '01/03/2017'),
    	(4, 'Obra A', '01/04/2017'),
    	(5, 'Obra B', '01/01/2017'),
    	(6, 'Obra B', '01/02/2017'),
    	(7, 'Obra B', '01/03/2017'),
    	(8, 'Obra B', '01/04/2017');
    
    DECLARE @Desglose table (
    ID INT NOT NULL,
    idReporte INT NOT NULL,
    Concepto varchar(50) NOT NULL,
    Horas decimal(9,2) NOT NULL,
    NumTrabajadores INT NOT NULL,
    PRIMARY KEY (ID)
    );
    
    INSERT INTO @Desglose
    	(ID, idReporte, Concepto,Horas,NumTrabajadores)
    VALUES
    	(1,1, 'Concepto A', 2,10 ),
    	(2, 1,'Concepto B', 3,12),
    	(3, 2,'Concepto A', 4,13),
    	(4, 2,'Concepto B', 12,5),
    	(5, 3,'Concepto A', 6,17),
    	(6, 3,'Concepto B', 8.5,8),
    	(7, 4,'Concepto A', 4,2),
    	(8, 4,'Concepto B', 3,6),
    	(9, 5,'Concepto A', 6,6),
    	(10, 5,'Concepto B', 7,6),
    	(11, 6,'Concepto A', 8,7),
    	(12, 6,'Concepto B', 3,12),
    	(13, 7,'Concepto A', 4,5),
    	(14, 7,'Concepto B', 5,8),
    	(15, 8,'Concepto A', 6,8),
    	(16, 8,'Concepto B', 7.5,8);
    La idea seria
    Nombre   HorasA           MediaEmpleadosA    HorasB           MediaEmpleadosB  HorasTotal          MediaEmpleadosTotal

    Obra A    Sum(1,3,5,7)   AVG(1,3,5,7)          Sum(2,4,6,8)   AVG(2,4,6,8)         HorasA+HorasB   MedEmpA + MedEmpB

    Obra B  Sum(9,11,13,15) AVG(9,11,13,15)  Sum(10,12,14,16) AVG(10,12,14,16) HorasA+HorasB   MedEmpA + MedEmpB

    No se si está bien explicado o si se entiende bien... De todos modos agradezco toda la ayuda que me podáis dar. Un saludo y gracias por vuestro tiempo :).


    Animo!

    jueves, 30 de marzo de 2017 15:24

Respuestas

  • Marc Marcus Wallace,

    Si sólo tienes dos conceptos entonces basta con transponer las filas a columna de manera fija (agrupando, definiendo las columnas y escribiendo los valores), por ejemplo:

    SELECT
        r.Nombre AS [Nombre],
        SUM(CASE WHEN d.Concepto = 'Concepto A' THEN d.Horas END) AS [HorasA],
        AVG(CASE WHEN d.Concepto = 'Concepto A' THEN d.NumTrabajadores END) AS [AVGEmpleadosA],
        SUM(CASE WHEN d.Concepto = 'Concepto B' THEN d.Horas END) AS [HorasB],
        AVG(CASE WHEN d.Concepto = 'Concepto B' THEN d.NumTrabajadores END) AS [AVGEmpleadosB],
        SUM(d.Horas) AS [HorasTotal],
        AVG(d.NumTrabajadores) AS [MediaEmpleadosTotal]
    FROM
        @Reporte r
        INNER JOIN @Desglose d ON (r.ID = d.idReporte)
    GROUP BY
        r.Nombre;
    GO


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    jueves, 30 de marzo de 2017 17:51

Todas las respuestas

  • Hola Marc Marcus Wallace,

    Revisa estos dos links: 

    Pivot de filas a columnas

    Pivot dinámico

    Saludos.


    Si serví a tu consulta, no olvides votar como útil.

    jueves, 30 de marzo de 2017 15:41
  • Marc Marcus Wallace,

    Si sólo tienes dos conceptos entonces basta con transponer las filas a columna de manera fija (agrupando, definiendo las columnas y escribiendo los valores), por ejemplo:

    SELECT
        r.Nombre AS [Nombre],
        SUM(CASE WHEN d.Concepto = 'Concepto A' THEN d.Horas END) AS [HorasA],
        AVG(CASE WHEN d.Concepto = 'Concepto A' THEN d.NumTrabajadores END) AS [AVGEmpleadosA],
        SUM(CASE WHEN d.Concepto = 'Concepto B' THEN d.Horas END) AS [HorasB],
        AVG(CASE WHEN d.Concepto = 'Concepto B' THEN d.NumTrabajadores END) AS [AVGEmpleadosB],
        SUM(d.Horas) AS [HorasTotal],
        AVG(d.NumTrabajadores) AS [MediaEmpleadosTotal]
    FROM
        @Reporte r
        INNER JOIN @Desglose d ON (r.ID = d.idReporte)
    GROUP BY
        r.Nombre;
    GO


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    jueves, 30 de marzo de 2017 17:51
  • Muchas gracias Brayan, Tienes toda la razón, no se porque me habia liado con pivotes y tal.

    Lo que si queria comentar una cosa y es que el campo MediaEmpleadosTotal es la Media de uno + la media del otro, no la media del conjunto completo, no se si me explico... Es decir si AVGEmpleadosA es 7 y AVGEmpleadosB es 10 el total deberia de ser 17. Para hacer eso he referenciado los campos AVGEmpleadosA y B pero no me los reconoce y he tenido que volver a poner toda la linea completa de AVG(CASE... Hay alguna manera de poderlo poner directamente? Osea... si ya esta calculado en AVGEmpleadosA pues sacarlo directo de ahí.

    Saludos cordiales y gracias por vuestro tiempo.


    Animo!

    viernes, 31 de marzo de 2017 8:42
  • Marc Marcus Wallace,

    Vamos, entonces el nombre de las columnas no indica claramente el valor que contiene, quizá deba llamarse [SumPromedios]. 

    Por otro lado, no puedes hacer uso de un alias de columna dentro del mismo contexto de selección, el resultado de la expresión no tiene visibilidad dentro de la cláusula SELECT, por lo que haces bien en escribir nuevamente las expresiones para calcular la sumatoria de promedios.



    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    viernes, 31 de marzo de 2017 15:36