none
MOSTRAR REPORTE CON PIVOT O CURSOR. RRS feed

  • Pregunta

  • Hola buenas tardes, tengo una consulta. espero puedan responder lo más pronto posible. (05/10/2019 11:00 am)

    tengo la siguiente base de datos con 2 tablas.

    create database tienda
    go
    use tienda
    
    create table vendedor (
    dni char(8) not null,
    nombre varchar(50),
    primary key (dni)
    )
    go
    
    create table venta (
    codigo char(2) not null,
    nombre varchar(50),
    cantidadvendida int,
    fechaventa date,
    primary key (codigo),
    dni char(8)
    foreign key (dni) references vendedor (dni)
    )
    go
    
    insert into vendedor values ('11223344','carla agreda')
    insert into vendedor values ('22334455','jeny perez')
    insert into vendedor values ('33445566','juan perez')
    insert into venta values ('v1','radio 200w',1,'01/01/2018','33445566')
    insert into venta values ('v2','refrigerador',1,'05/01/2018','33445566')
    insert into venta values ('v3','tv 41 lg',1,'01/02/2018','22334455')
    insert into venta values ('v4','radio 200w',1,'05/02/2018','22334455')
    insert into venta values ('v5','radio 200w',1,'02/01/2019','11223344')
    insert into venta values ('v6','tv 41 lg',1,'10/01/2019','11223344')
    insert into venta values ('v7','tv 41 lg',1,'02/02/2019','11223344')
    insert into venta values ('v8','radio 200w',1,'10/02/2019','22334455')

    Lo que deseo obtener es el siguiente informe:

                       ||2018         ||2018            ||2018       ||2019           ||2019             ||2019         

    Vendedor     ||radio 200w || refrigerador || tv 41 lg  || radio 200w || refrigerador  || tv 41 lg
    carla agreda                                                              1                                         2
    jeny perez             1                                       1               1                                        

    juan perez            1                    1

    espero que se entienda el resultado.

    NOTA: Talvez al generar las cantidades en la tabla de ejemplo me eh confundido.

    tengo un PIVOT Dinámico que ejecuta pero solo me sale lo siguiente:

    DECLARE @consulta VARCHAR(4000)
    DECLARE @nombreproducto VARCHAR(4000)
    
     SELECT @nombreproducto =
    
           STUFF((SELECT DISTINCT '],['+LTRIM([nombre]) FROM venta 
    		ORDER BY '],['+LTRIM([nombre]) FOR XML PATH('') ),1,2,'') + ']'
    
    SET @consulta=
    'SELECT * FROM
      (SELECT v.nombre vend,vta.nombre prod, SUM (vta.cantidadvendida) cantidad
       FROM vendedor v 
       inner join venta vta
       on v.dni=vta.dni
       group by v.nombre, vta.nombre
       ) T
    PIVOT(SUM(T.cantidad)FOR T.prod IN ('+ @nombreproducto +')) PVT ' EXECUTE (@consulta)

    AYUDA URGENTEEEEE!!!!!!

    viernes, 4 de octubre de 2019 22:10

Respuestas

  • Hola JOSMELL125:

    Para ese escenario:

    DECLARE @CONSULTA NVARCHAR(4000);
    DECLARE @NOMBREPRODUCTO VARCHAR(4000);
    DECLARE @NOMBREPRODUCTO2 VARCHAR(4000);
    
    DECLARE @CABECERA VARCHAR(4000);
    DECLARE @CABECERAANUAL VARCHAR(4000);
    
    SELECT @NOMBREPRODUCTO =
           STUFF((SELECT DISTINCT '],['+LTRIM(CONCAT(YEAR(FECHAVENTA),' - ',NOMBRE))  FROM VENTA 
    		ORDER BY '],['+LTRIM(CONCAT(YEAR(FECHAVENTA),' - ',NOMBRE)) FOR XML PATH('') ),1,2,'') + ']'
    
    
    
    DECLARE @TABLE TABLE (ANUAL INT)
        INSERT INTO @TABLE 
    	   SELECT YEAR(FECHAVENTA)
    		  FROM VENTA
    	   GROUP BY YEAR (FECHAVENTA);
    
    
    DECLARE @TABLECABECERAS TABLE (VALOR VARCHAR(100))
    INSERT INTO @TABLECABECERAS 
    
            SELECT CONCAT('''',T.NOMBRE,'''',' AS ', QUOTENAME(LTRIM([T].ANUAL))) FROM 
    	   (
    	       SELECT TOP(100)PERCENT T.ANUAL, FN.NAME, FN.NOMBRE FROM @TABLE T
    			 LEFT JOIN (
    					   SELECT COUNT(DISTINCT NOMBRE) AS NAME, NOMBRE, YEAR(FECHAVENTA) ANUAL FROM VENTA
    					   GROUP BY NOMBRE, YEAR(FECHAVENTA)
    					 ) FN ON FN.ANUAL = T.ANUAL
    	      ORDER BY T.ANUAL
    	   ) AS T
    		ORDER BY '],['+LTRIM([T].ANUAL) ;
    
    SELECT @CABECERAANUAL = CONCAT(@CABECERAANUAL ,T.VALOR, ',') FROM @TABLECABECERAS AS T;
    SET @CABECERAANUAL = CONCAT('SELECT NULL AS VEND, ',LEFT(@CABECERAANUAL,(LEN(@CABECERAANUAL)-1)), N' 
    UNION ALL
    SELECT PVT.[VEND], ');
    
    
    SET @NOMBREPRODUCTO2 = @NOMBREPRODUCTO
    SET @NOMBREPRODUCTO2 = REPLACE(@NOMBREPRODUCTO,'[','CAST (PVT.[');
    SET @NOMBREPRODUCTO2 = REPLACE(@NOMBREPRODUCTO2,']', '] AS VARCHAR(100))');
    
    SET @CONSULTA = CONCAT(@CABECERAANUAL, @NOMBREPRODUCTO2, N' FROM
      (SELECT v.nombre vend,CONCAT(YEAR(fechaventa),'' - '',vta.nombre) prod, SUM (vta.cantidadvendida) cantidad
       FROM vendedor v 
       inner join venta vta
       on v.dni=vta.dni
       group by v.nombre, vta.nombre, year(fechaventa)
       ) T
    PIVOT(SUM(T.cantidad)FOR T.prod IN ('+ @nombreproducto +')) PVT ;
    ');
    
    EXEC sp_ExecuteSql @consulta 
    
    

    Solución

    • Marcado como respuesta JOSMELL125 martes, 8 de octubre de 2019 16:01
    domingo, 6 de octubre de 2019 4:10
  • Hola Javi Fernández F buen día.

    Muchas Gracias por la Respuesta, es justamente lo que buscaba.

    Se le Agradece el tiempo dedicado, el conocimiento compartido y las buenas vibras.

    Nuevamente Gracias Genio. 

    • Marcado como respuesta JOSMELL125 martes, 8 de octubre de 2019 16:05
    martes, 8 de octubre de 2019 16:05

Todas las respuestas

  • Hola JOSMELL125:

    No es buen camino solicitar ayuda, exigiendo día y hora, y después "GRITANDO", ya que muchas personas en los foros consideran la escritura en mayúsculas como tal.

    Lo que solicitas suele ser cosa del software cliente, pero puedes hacerlo algo como así:

    DECLARE @consulta VARCHAR(4000)
    DECLARE @nombreproducto VARCHAR(4000)
    
      SELECT @nombreproducto =
    
           STUFF((SELECT DISTINCT '],['+LTRIM(CONCAT(YEAR(fechaventa),' - ',nombre))  FROM venta 
    		ORDER BY '],['+LTRIM(CONCAT(YEAR(fechaventa),' - ',nombre)) FOR XML PATH('') ),1,2,'') + ']'
    
    SET @consulta=
    'SELECT * FROM
      (SELECT v.nombre vend,CONCAT(YEAR(fechaventa),'' - '',vta.nombre) prod, SUM (vta.cantidadvendida) cantidad
       FROM vendedor v 
       inner join venta vta
       on v.dni=vta.dni
       group by v.nombre, vta.nombre, year(fechaventa)
       ) T
    PIVOT(SUM(T.cantidad)FOR T.prod IN ('+ @nombreproducto +')) PVT 
    
    ' EXECUTE (@consulta)

    Salida

    sábado, 5 de octubre de 2019 5:58
  • Hola Javi Fernández, 

    antes que Nada pedir las disculpas del caso ya que en ningún momento mi intención fue gritar y exigir tiempo para que me puedan ayudar. es la primera vez que realizo una consulta por este medio y lo que menos quiero es exigir cuando es un foro de ayuda y además no tenía conocimiento de que las letras mayúsculas significaban gritos.

    Por Favor las Disculpas del caso.

    Ahora con respecto a la respuesta que planteas te agradezco por el tiempo tomado es de mucha utilidad y lo tomaré como ejemplo pero el resultado que busco es colocar el año en otra fila arriba de cada producto.

    un reporte por año de cada producto vendido y que vendedor fue el que realizó la venta.

    Muchas Gracias.


    • Editado JOSMELL125 sábado, 5 de octubre de 2019 18:48
    sábado, 5 de octubre de 2019 18:02
  • Hola JOSMELL125:

    Por Favor las Disculpas del caso.

    No problema. Por mi parte disculpas aceptadas.

    En cuanto a lo que solicitas. Hay un pequeño problema que a lo mejor no has parado a pensar.

    pero el resultado que busco es colocar el año en otra fila arriba de cada producto.

    Entonces los alias de columna serían [2018] [2018] [2019] [2019]. Los tipos de datos, obligatoriamente serían varchar o nvarchar porque la primera fila contendría

    FILA 1 = [NULL], 'radio 200w', 'refrigerador', …..

    FILA 2 = CARLA, null, null, null, 1, 2

    FILA 3 = JENY, 1, null ....

    ¿es esto lo que quieres?

    domingo, 6 de octubre de 2019 3:15
  • Hola JOSMELL125:

    Para ese escenario:

    DECLARE @CONSULTA NVARCHAR(4000);
    DECLARE @NOMBREPRODUCTO VARCHAR(4000);
    DECLARE @NOMBREPRODUCTO2 VARCHAR(4000);
    
    DECLARE @CABECERA VARCHAR(4000);
    DECLARE @CABECERAANUAL VARCHAR(4000);
    
    SELECT @NOMBREPRODUCTO =
           STUFF((SELECT DISTINCT '],['+LTRIM(CONCAT(YEAR(FECHAVENTA),' - ',NOMBRE))  FROM VENTA 
    		ORDER BY '],['+LTRIM(CONCAT(YEAR(FECHAVENTA),' - ',NOMBRE)) FOR XML PATH('') ),1,2,'') + ']'
    
    
    
    DECLARE @TABLE TABLE (ANUAL INT)
        INSERT INTO @TABLE 
    	   SELECT YEAR(FECHAVENTA)
    		  FROM VENTA
    	   GROUP BY YEAR (FECHAVENTA);
    
    
    DECLARE @TABLECABECERAS TABLE (VALOR VARCHAR(100))
    INSERT INTO @TABLECABECERAS 
    
            SELECT CONCAT('''',T.NOMBRE,'''',' AS ', QUOTENAME(LTRIM([T].ANUAL))) FROM 
    	   (
    	       SELECT TOP(100)PERCENT T.ANUAL, FN.NAME, FN.NOMBRE FROM @TABLE T
    			 LEFT JOIN (
    					   SELECT COUNT(DISTINCT NOMBRE) AS NAME, NOMBRE, YEAR(FECHAVENTA) ANUAL FROM VENTA
    					   GROUP BY NOMBRE, YEAR(FECHAVENTA)
    					 ) FN ON FN.ANUAL = T.ANUAL
    	      ORDER BY T.ANUAL
    	   ) AS T
    		ORDER BY '],['+LTRIM([T].ANUAL) ;
    
    SELECT @CABECERAANUAL = CONCAT(@CABECERAANUAL ,T.VALOR, ',') FROM @TABLECABECERAS AS T;
    SET @CABECERAANUAL = CONCAT('SELECT NULL AS VEND, ',LEFT(@CABECERAANUAL,(LEN(@CABECERAANUAL)-1)), N' 
    UNION ALL
    SELECT PVT.[VEND], ');
    
    
    SET @NOMBREPRODUCTO2 = @NOMBREPRODUCTO
    SET @NOMBREPRODUCTO2 = REPLACE(@NOMBREPRODUCTO,'[','CAST (PVT.[');
    SET @NOMBREPRODUCTO2 = REPLACE(@NOMBREPRODUCTO2,']', '] AS VARCHAR(100))');
    
    SET @CONSULTA = CONCAT(@CABECERAANUAL, @NOMBREPRODUCTO2, N' FROM
      (SELECT v.nombre vend,CONCAT(YEAR(fechaventa),'' - '',vta.nombre) prod, SUM (vta.cantidadvendida) cantidad
       FROM vendedor v 
       inner join venta vta
       on v.dni=vta.dni
       group by v.nombre, vta.nombre, year(fechaventa)
       ) T
    PIVOT(SUM(T.cantidad)FOR T.prod IN ('+ @nombreproducto +')) PVT ;
    ');
    
    EXEC sp_ExecuteSql @consulta 
    
    

    Solución

    • Marcado como respuesta JOSMELL125 martes, 8 de octubre de 2019 16:01
    domingo, 6 de octubre de 2019 4:10
  • Hola Javi Fernández F buen día.

    Muchas Gracias por la Respuesta, es justamente lo que buscaba.

    Se le Agradece el tiempo dedicado, el conocimiento compartido y las buenas vibras.

    Nuevamente Gracias Genio. 

    • Marcado como respuesta JOSMELL125 martes, 8 de octubre de 2019 16:05
    martes, 8 de octubre de 2019 16:05
  • Un placer poder aportar un granito de arena.
    martes, 8 de octubre de 2019 19:11