none
Matriz en SQL por query RRS feed

  • Pregunta

  • Hola,

    Existe alguna manera de hacer mediante un query en SQL una matriz basada en las filas de una tabla como columnas y las filas de otra tabla como renglones?

    Gracias


    Michel Cochegrus

    lunes, 21 de septiembre de 2020 19:14

Respuestas

  • Existen varias maneras de hacer eso. Te expongo una, el pivot estático, y con solo 10 columnas, como ejemplo que realmente serían tantos como días del mes.

    Pero lo primero, el escenario:

    Create table dbo.Productos (idProducto int, producto varchar(100))
    Create table dbo.Ventas (folio int, producto int, fecha date)
    GO
    insert into dbo.Productos (idProducto, producto)
    values
    (1,'Producto A'),
    (2,'Producto B'),
    (3,'Producto C');
    
    Insert into dbo.Ventas (folio, producto, fecha)
    values
    (1 ,1,'20200901'),
    (2 ,1,'20200901'),
    (3 ,1,'20200901'),
    (4 ,2,'20200901'),
    (5 ,3,'20200902'),
    (6 ,1,'20200902'),
    (7 ,2,'20200903'),
    (8 ,3,'20200905'),
    (9 ,1,'20200906'),
    (10,2,'20200907'),
    (11,3,'20200907'),
    (12,1,'20200908'),
    (13,2,'20200908'),
    (14,3,'20200908'),
    (15,3,'20200908');
    

    Ahora con el escenario vamos a montar un pivote.

    Select p.idProducto
    , p.producto
    , isnull(pvt.[1] ,0) as [día 1]
    , isnull(pvt.[2] ,0) as [día 2]
    , isnull(pvt.[3] ,0) as [día 3]
    , isnull(pvt.[4] ,0) as [día 4]
    , isnull(pvt.[5] ,0) as [día 5]
    , isnull(pvt.[6] ,0) as [día 6]
    , isnull(pvt.[7] ,0) as [día 7]
    , isnull(pvt.[8] ,0) as [día 8]
    , isnull(pvt.[9] ,0) as [día 9]
    , isnull(pvt.[10],0) as [día 10]
    
    from (
    	Select count(*) as venta
    			,v.producto as producto
    			, datepart(day,v.fecha) as dia
    			from 
    				dbo.Ventas v 
    			group by datepart(day,v.fecha),v.producto
    		) as source
    	pivot (sum(venta) for dia in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) as pvt
    	inner join dbo.Productos p on pvt.producto = p.idProducto

    La gracia del pivot, está en que puedes trasponer filas por columnas, y lo que la tabla derivada source le devuelve como una serie de filas, este lo transforma en una suma de valores para columnas.

    Es estático, porque depende de valores fijos, como son los días del mes.

    Pivot simple paso a paso

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

    Otra manera de hacerlo, se llama trasponer filas a columnas. 

    https://stackoverrun.com/es/q/5492327

    Y por último y la más potente, es el pivot dinámico. Es un poco más complejo, pero no dependes de los valores que quieres (fijos días 1 a 31 del mes), sino que se configura dinámicamente, por lo que tiene la tabla de ventas.

    Pivot dinámico

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

    • Marcado como respuesta mcochegrus lunes, 21 de septiembre de 2020 22:17
    lunes, 21 de septiembre de 2020 21:08
  • La verdad es que no entiendo muy bien que sea difícil completar un simple ejemplo de una tabla con valores ficticios, donde se pueda ver realmente lo que quieres obtener, ya que no tiene nada que ver si la suma de los contenidos, es para 12 meses y luego los datos por año o en realidad los meses son de todos los periodos y los años separados. Ya que hacer un doble pivote, por el mero hecho de hacerlo no creo que sea el objetivo, sino más bien obtener la solución que necesitas para los datos que tienes que trabajar. Pero, si lo que quieres es un ejemplo de doble pivot.

    Drop Table If EXISTS dbo.Productos;
    Drop Table if exists dbo.Ventas;
    Create table dbo.Productos (idProducto int, producto varchar(100))
    Create table dbo.Ventas (folio int, producto int, fecha date)
    GO
    insert into dbo.Productos (idProducto, producto)
    values
    (1,'Producto A'),
    (2,'Producto B'),
    (3,'Producto C');
    
    Insert into dbo.Ventas (folio, producto, fecha)
    values
    (1 ,1,'20200901'),
    (2 ,1,'20200901'),
    (3 ,1,'20200901'),
    (4 ,2,'20200901'),
    (5 ,3,'20200902'),
    (6 ,1,'20200902'),
    (7 ,2,'20200903'),
    (8 ,3,'20200905'),
    (9 ,1,'20200906'),
    (10,2,'20200907'),
    (11,3,'20200907'),
    (12,1,'20200908'),
    (13,2,'20200908'),
    (14,3,'20200908'),
    (15,3,'20200908'),
    (21 ,1,'20190901'),
    (22 ,1,'20190901'),
    (23 ,1,'20190901'),
    (24 ,2,'20190901'),
    (25 ,3,'20190902'),
    (26 ,1,'20190902'),
    (27 ,2,'20190903'),
    (28 ,3,'20190905'),
    (29 ,1,'20190906'),
    (30,2,'20190907'),
    (31,3,'20190907'),
    (32,1,'20190908'),
    (33,2,'20190908'),
    (34,3,'20190908'),
    (35,3,'20190908');
    GO
    

    Dos años de periodo.

    La manera más fácil, es utilizar tablas de expresión común correlativas, donde puedes tratar a los conjuntos como temas independientes, y de ahí obtener o tratar los mismos con facilidad

    WITH c
    	 as (Select p.idProducto
    			  , p.producto
    			  , anual
    			  , isnull(pvt.[1], 0) as [día 1]
    			  , isnull(pvt.[2], 0) as [día 2]
    			  , isnull(pvt.[3], 0) as [día 3]
    			  , isnull(pvt.[4], 0) as [día 4]
    			  , isnull(pvt.[5], 0) as [día 5]
    			  , isnull(pvt.[6], 0) as [día 6]
    			  , isnull(pvt.[7], 0) as [día 7]
    			  , isnull(pvt.[8], 0) as [día 8]
    			  , isnull(pvt.[9], 0) as [día 9]
    			  , isnull(pvt.[10], 0) as [día 10]
    				from (
    					   Select count(*) as venta
    							, v.producto as producto
    							, datepart(day, v.fecha) as dia
    							, year(v.fecha) as anual
    							  from dbo.Ventas AS v
    							  group by datepart(day, v.fecha)
    									 , v.producto
    									 , year(v.fecha)
    					 ) as source pivot(sum(venta) for dia in([1]
    														   , [2]
    														   , [3]
    														   , [4]
    														   , [5]
    														   , [6]
    														   , [7]
    														   , [8]
    														   , [9]
    														   , [10])) as pvt
    						  inner join dbo.Productos AS p on pvt.producto = p.idProducto),
    	 c1
    	 as (Select c.idProducto
    			  , c.producto
    			  , c.anual
    			  , c.[día 1]
    			  , c.[día 2]
    			  , c.[día 3]
    			  , c.[día 4]
    			  , c.[día 5]
    			  , c.[día 6]
    			  , c.[día 7]
    			  , c.[día 8]
    			  , c.[día 9]
    			  , c.[día 10]
    			  , c.[día 1]+c.[día 2]+c.[día 3]
    			   + c.[día 4]+c.[día 5]+c.[día 6]
    			   + c.[día 7]+c.[día 8]+c.[día 9]
    			   + c.[día 10] as sumaDias
    				from c)
    	 Select *
    			from c1
    pivot (sum(sumaDias) for anual in ([2019],[2020])) pvt

    Tablas de expresión común correlativas.

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

    • Marcado como respuesta mcochegrus miércoles, 23 de septiembre de 2020 15:28
    miércoles, 23 de septiembre de 2020 4:44

Todas las respuestas

  • Hola Michel Cochegrus:

    Porque no específicas un poco lo que tienes y lo que quieres, con un pequeño ejemplo, y buscamos una solución.


    lunes, 21 de septiembre de 2020 19:55
  • Por ejemplo, dada una tabla Productos con N renglones (idproducto, producto)

    y otra tabla con ventas con N renglones de productos donde (folio, producto, fecha)

    el resultado que quisiera es una matriz donde los renglones sean los productos y las columnas los días del mes que se vendieron ese producto del renglón(donde no hubo ventas muestre 0)


    Michel Cochegrus

    lunes, 21 de septiembre de 2020 20:18
  • Existen varias maneras de hacer eso. Te expongo una, el pivot estático, y con solo 10 columnas, como ejemplo que realmente serían tantos como días del mes.

    Pero lo primero, el escenario:

    Create table dbo.Productos (idProducto int, producto varchar(100))
    Create table dbo.Ventas (folio int, producto int, fecha date)
    GO
    insert into dbo.Productos (idProducto, producto)
    values
    (1,'Producto A'),
    (2,'Producto B'),
    (3,'Producto C');
    
    Insert into dbo.Ventas (folio, producto, fecha)
    values
    (1 ,1,'20200901'),
    (2 ,1,'20200901'),
    (3 ,1,'20200901'),
    (4 ,2,'20200901'),
    (5 ,3,'20200902'),
    (6 ,1,'20200902'),
    (7 ,2,'20200903'),
    (8 ,3,'20200905'),
    (9 ,1,'20200906'),
    (10,2,'20200907'),
    (11,3,'20200907'),
    (12,1,'20200908'),
    (13,2,'20200908'),
    (14,3,'20200908'),
    (15,3,'20200908');
    

    Ahora con el escenario vamos a montar un pivote.

    Select p.idProducto
    , p.producto
    , isnull(pvt.[1] ,0) as [día 1]
    , isnull(pvt.[2] ,0) as [día 2]
    , isnull(pvt.[3] ,0) as [día 3]
    , isnull(pvt.[4] ,0) as [día 4]
    , isnull(pvt.[5] ,0) as [día 5]
    , isnull(pvt.[6] ,0) as [día 6]
    , isnull(pvt.[7] ,0) as [día 7]
    , isnull(pvt.[8] ,0) as [día 8]
    , isnull(pvt.[9] ,0) as [día 9]
    , isnull(pvt.[10],0) as [día 10]
    
    from (
    	Select count(*) as venta
    			,v.producto as producto
    			, datepart(day,v.fecha) as dia
    			from 
    				dbo.Ventas v 
    			group by datepart(day,v.fecha),v.producto
    		) as source
    	pivot (sum(venta) for dia in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) as pvt
    	inner join dbo.Productos p on pvt.producto = p.idProducto

    La gracia del pivot, está en que puedes trasponer filas por columnas, y lo que la tabla derivada source le devuelve como una serie de filas, este lo transforma en una suma de valores para columnas.

    Es estático, porque depende de valores fijos, como son los días del mes.

    Pivot simple paso a paso

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

    Otra manera de hacerlo, se llama trasponer filas a columnas. 

    https://stackoverrun.com/es/q/5492327

    Y por último y la más potente, es el pivot dinámico. Es un poco más complejo, pero no dependes de los valores que quieres (fijos días 1 a 31 del mes), sino que se configura dinámicamente, por lo que tiene la tabla de ventas.

    Pivot dinámico

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

    • Marcado como respuesta mcochegrus lunes, 21 de septiembre de 2020 22:17
    lunes, 21 de septiembre de 2020 21:08
  • michas gracias, lo pondré en práctica y te digo como salió!

    Michel Cochegrus

    lunes, 21 de septiembre de 2020 21:31
  • Se puede hacer pívot sobre pívot?

    Michel Cochegrus

    lunes, 21 de septiembre de 2020 22:17
  • Si se puede
    martes, 22 de septiembre de 2020 3:10
  • Y como se hace ya que no puedo relacionar las nuevas columnas. Este es mi código:

    	select * from (
    	select year(c.fecha) as [año],DATEPART( wk, c.fecha) as [Semana],c.ClaveProducto,i.c2 as [Producto],c.unidad as Unidades
    	from kdii i
    	left join v_cubo_Comparativo c on (c.ClaveProducto=i.c1)
    	where i.c3 like '00%' and c.Genero='X'and c.Fecha>=@fecha_ini and c.fecha<=@fecha_fin
    	
    	) as Source
    	pivot( sum(unidades) for semana in 
    	([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],
    	[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53])
    	) as PVT

    y da como resultado: 

    La idea es hacer el segundo pívot por año


    Michel Cochegrus


    • Editado mcochegrus martes, 22 de septiembre de 2020 13:08
    martes, 22 de septiembre de 2020 13:07
  • Pero en ese escenario, ¿cuál es el resultado a obtener?.

    Pon dos ejemplos:

    Product = baclac10

    Product = baclac20

    martes, 22 de septiembre de 2020 13:38
  • Creo que es más fácil que me expliques cual sería un ejemplo de doble pivot. 

    Michel Cochegrus

    martes, 22 de septiembre de 2020 20:47
  • La verdad es que no entiendo muy bien que sea difícil completar un simple ejemplo de una tabla con valores ficticios, donde se pueda ver realmente lo que quieres obtener, ya que no tiene nada que ver si la suma de los contenidos, es para 12 meses y luego los datos por año o en realidad los meses son de todos los periodos y los años separados. Ya que hacer un doble pivote, por el mero hecho de hacerlo no creo que sea el objetivo, sino más bien obtener la solución que necesitas para los datos que tienes que trabajar. Pero, si lo que quieres es un ejemplo de doble pivot.

    Drop Table If EXISTS dbo.Productos;
    Drop Table if exists dbo.Ventas;
    Create table dbo.Productos (idProducto int, producto varchar(100))
    Create table dbo.Ventas (folio int, producto int, fecha date)
    GO
    insert into dbo.Productos (idProducto, producto)
    values
    (1,'Producto A'),
    (2,'Producto B'),
    (3,'Producto C');
    
    Insert into dbo.Ventas (folio, producto, fecha)
    values
    (1 ,1,'20200901'),
    (2 ,1,'20200901'),
    (3 ,1,'20200901'),
    (4 ,2,'20200901'),
    (5 ,3,'20200902'),
    (6 ,1,'20200902'),
    (7 ,2,'20200903'),
    (8 ,3,'20200905'),
    (9 ,1,'20200906'),
    (10,2,'20200907'),
    (11,3,'20200907'),
    (12,1,'20200908'),
    (13,2,'20200908'),
    (14,3,'20200908'),
    (15,3,'20200908'),
    (21 ,1,'20190901'),
    (22 ,1,'20190901'),
    (23 ,1,'20190901'),
    (24 ,2,'20190901'),
    (25 ,3,'20190902'),
    (26 ,1,'20190902'),
    (27 ,2,'20190903'),
    (28 ,3,'20190905'),
    (29 ,1,'20190906'),
    (30,2,'20190907'),
    (31,3,'20190907'),
    (32,1,'20190908'),
    (33,2,'20190908'),
    (34,3,'20190908'),
    (35,3,'20190908');
    GO
    

    Dos años de periodo.

    La manera más fácil, es utilizar tablas de expresión común correlativas, donde puedes tratar a los conjuntos como temas independientes, y de ahí obtener o tratar los mismos con facilidad

    WITH c
    	 as (Select p.idProducto
    			  , p.producto
    			  , anual
    			  , isnull(pvt.[1], 0) as [día 1]
    			  , isnull(pvt.[2], 0) as [día 2]
    			  , isnull(pvt.[3], 0) as [día 3]
    			  , isnull(pvt.[4], 0) as [día 4]
    			  , isnull(pvt.[5], 0) as [día 5]
    			  , isnull(pvt.[6], 0) as [día 6]
    			  , isnull(pvt.[7], 0) as [día 7]
    			  , isnull(pvt.[8], 0) as [día 8]
    			  , isnull(pvt.[9], 0) as [día 9]
    			  , isnull(pvt.[10], 0) as [día 10]
    				from (
    					   Select count(*) as venta
    							, v.producto as producto
    							, datepart(day, v.fecha) as dia
    							, year(v.fecha) as anual
    							  from dbo.Ventas AS v
    							  group by datepart(day, v.fecha)
    									 , v.producto
    									 , year(v.fecha)
    					 ) as source pivot(sum(venta) for dia in([1]
    														   , [2]
    														   , [3]
    														   , [4]
    														   , [5]
    														   , [6]
    														   , [7]
    														   , [8]
    														   , [9]
    														   , [10])) as pvt
    						  inner join dbo.Productos AS p on pvt.producto = p.idProducto),
    	 c1
    	 as (Select c.idProducto
    			  , c.producto
    			  , c.anual
    			  , c.[día 1]
    			  , c.[día 2]
    			  , c.[día 3]
    			  , c.[día 4]
    			  , c.[día 5]
    			  , c.[día 6]
    			  , c.[día 7]
    			  , c.[día 8]
    			  , c.[día 9]
    			  , c.[día 10]
    			  , c.[día 1]+c.[día 2]+c.[día 3]
    			   + c.[día 4]+c.[día 5]+c.[día 6]
    			   + c.[día 7]+c.[día 8]+c.[día 9]
    			   + c.[día 10] as sumaDias
    				from c)
    	 Select *
    			from c1
    pivot (sum(sumaDias) for anual in ([2019],[2020])) pvt

    Tablas de expresión común correlativas.

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

    • Marcado como respuesta mcochegrus miércoles, 23 de septiembre de 2020 15:28
    miércoles, 23 de septiembre de 2020 4:44
  • La salida de la query es la suma de los datos por día y la suma de los datos por año.

    miércoles, 23 de septiembre de 2020 4:45
  • Perfecto Javier, es justo lo que esperaba! Lo estudio y te comento como quedó!

    Michel Cochegrus

    miércoles, 23 de septiembre de 2020 15:28