SQL 2005, la sentencia Pivot y la solucion en SQL 2000

Introducción

El SQL Server 2005 incorpora una funcionalidad denominada Pivot, la misma permite transformar filas en columnas de manera de poder mostrar datos cruzados.

Problemática

Supongamos el siguiente escenario, una empresa dedicada ala perfumeria desea conocer el resultado de las ventas de los 3 Rubros que comercializa , para cada vendedor. Entonces desde el lado de programación de la base de datos nos disponemos a realizar el select correspondiente, pero… como expresamos cada uno de los rubros en la misma consulta si la fuente de origen es la misma? Analicemos las siguientes estructuras y veremos que la información principal se encuentra en la tabla ventas, siendo alimentada por las tablas de Vendedores, Productos y RubrosVendedores

IDVendedor	Descripción
1	Juan Perez
2	Maria Vazquez
3	Rocio Marquez

Solución a la problemática en SQL 2000

Primero realizaremos una consulta que nos devuelva la cantidad de rubros que vendio cada vendedor, nuestro objetivo final será que cada rubro sea una columna y exista solo una fila de vendedores con la cantidad vendida de cada rubro:

Select 
cast(vnd.descripcion as char(25)) as Vendedor, cast(Rbr.descripcion as char(20)) as Rubro ,
sum(cantidad) as Cantidad
From 
ventas vts inner join vendedores vnd 
on vts.idvendedor=vnd.idvendedor
inner join Productos Prd 
on Prd.idproducto=vts.idProducto
inner join Rubros Rbr on Rbr.idRubro=Prd.idRubro
group by vnd.descripcion, Rbr.descripcion

El resultado será

Vendedor                  Rubro                Cantidad
------------------------- -------------------- ----------------------
Maria Vazquez             Cremas               30
Rocio Marquez             Cremas               50
Juan Perez                Desodorantes         20
Rocio Marquez             Desodorantes         100
Juan Perez                Perfumes             10
Maria Vazquez             Perfumes             5

Pero lo que nosotros queremos lograr es en una sola linea la informacion de cada vendedor, entonces podemos ir desglosando la consulta según el rubro e ir uniendola

Vendedor	Cremas	Perfumes	Desodorantes
Juan Perez	CantCremas	CantPerfumes	CantDesodorantes
Maria Vazquez	CantCremas	CantPerfumes	CantDesodorantes
Rocio Marquez	CantCremas	CantPerfumes	CantDesodorantes

Para esto analizamos rubro por rubro y unimos las consultas, de manera que vamos teniendo los totales por vendedores por rubros, pero aun sin sumarizar a cada vendedor en una sola linea 

Select 
cast(vnd.descripcion as char(20)) Vendedor, sum(cantidad) as Cantidad_Rubro1, 
0 as Cantidad_rubro2,
0 as Cantidad_Rubro3
From 
ventas vts inner join vendedores vnd
on vts.idvendedor=vnd.idvendedor
inner join Productos Prd 
on Prd.idproducto=vts.idProducto
Inner join Rubros Rbr on Rbr.idRubro=Prd.idRubro
where 
Rbr.idRubro=1
group 
by vnd.descripcion, rbr.descripcion

/*El primer query es para obtener la cantidad del rubro 1, por lo tanto el rubro 2 y 3 
de este query van a quedar en 0*/
/*Notese qe en el primer Query definimos como se van a llamar los campos en el resultado*/

Union  

Select 
cast(vnd.descripcion as char(20)) Vendedor, 0, sum(cantidad) ,0 
From 
ventas vts inner join vendedores vnd
on vts.idvendedor=vnd.idvendedor
inner join Productos Prd 
on Prd.idproducto=vts.idProducto
Inner join Rubros Rbr on Rbr.idRubro=Prd.idRubro
where 
Rbr.idRubro=2
group by 
vnd.descripcion, rbr.descripcion

/*El 2do query es para obtener la cantidad del rubro 2, por lo tanto el rubro 1 y 3 de este 
query van a quedar en 0*/

Union

Select 
cast(vnd.descripcion as char(20)) Vendedor, 0,0 ,sum(cantidad) 
From 
ventas vts inner join vendedores vnd
on vts.idvendedor=vnd.idvendedor
inner join Productos Prd 
on Prd.idproducto=vts.idProducto
Inner join Rubros Rbr on Rbr.idRubro=Prd.idRubro
where 
Rbr.idRubro=3
group     
by vnd.descripcion, rbr.descripcion


/*El 3er query es para obtener la cantidad del rubro 3, por lo tanto el rubro 2 y 3 de este 
query van a quedar en 0*/

Vendedor             Cantidad_Rubro1        Cantidad_rubro2        Cantidad_Rubro3
-------------------- ---------------------- ---------------------- ----------------------
Juan Perez           0                      20                     0
Juan Perez           10                     0                      0
Maria Vazquez        0                      0                      30
Maria Vazquez        5                      0                      0
Rocio Marquez        0                      0                      50
Rocio Marquez        0                      100                    0

(6 row(s) affected)

Select 
cast(Resultados.Vendedor as char(20)) Vendedor, 
Sum(Cantidad_Rubro1) as Cremas ,
Sum(Cantidad_rubro2) as Desodorantes,
Sum(Cantidad_Rubro3) as Perfumes
From(
Select 
vnd.descripcion as Vendedor, sum(cantidad) as Cantidad_Rubro1, 0 as Cantidad_rubro2,0 as 
Cantidad_Rubro3
From 
ventas vts inner join vendedores vnd
on vts.idvendedor=vnd.idvendedor
inner join Productos Prd 
on Prd.idproducto=vts.idProducto
Inner join Rubros Rbr on Rbr.idRubro=Prd.idRubro
where 
Rbr.idRubro=1
group 
by vnd.descripcion, rbr.descripcion
Union  

Select 
vnd.descripcion , 0, sum(cantidad) ,0 
From 
ventas vts inner join vendedores vnd
on vts.idvendedor=vnd.idvendedor
inner join Productos Prd 
on Prd.idproducto=vts.idProducto
Inner join Rubros Rbr on Rbr.idRubro=Prd.idRubro
where 
Rbr.idRubro=2
group by 
vnd.descripcion, rbr.descripcion
Union
Select 
vnd.descripcion , 0,0 ,sum(cantidad) 
From 
ventas vts inner join vendedores vnd
on vts.idvendedor=vnd.idvendedor
inner join Productos Prd 
on Prd.idproducto=vts.idProducto
Inner join Rubros Rbr on Rbr.idRubro=Prd.idRubro
where 
Rbr.idRubro=3
group     
by vnd.descripcion, rbr.descripcion) as Resultados
Group by Resultados.Vendedor

Vendedor             Cremas                 Desodorantes           Perfumes
-------------------- ---------------------- ---------------------- ----------------------
Juan Perez           10                     20                     0
Maria Vazquez        5                      0                      30
Rocio Marquez        0                      100                    50

(3 row(s) affected)

Que hicimos? Simplemente hicimos un select sobre una consulta,ya que en la consulta definimos los nombres de los campos que luego utilizamos en la selección. Estas consultas resultan un arma poderosa para resolver sumarizaciones de varios conceptos y presentar los datos de una manera sencilla.
El problema radica en la cantidad de veces que se ejecuta la consulta select, en este caso, es 1 por cada rubro que estamos analizando, es por ello el objetivo de este articulo de mostrar como la sentencia Pivot hace exactamente lo mismo que acabamos de realizar, de manera mas sencilla

Pivot en SQL 2005

Vamos a analizar la tabla de ventas pero con los joins correspondientes a las tablas de rubros y vendedores

Select 
cast(vnd.descripcion as char(20)) Vendedor, Cantidad , cast(rbr.descripcion as char(20))Rubro
From 
ventas vts inner join vendedores vnd
on vts.idvendedor=vnd.idvendedor
inner join Productos Prd 
on Prd.idproducto=vts.idProducto
Inner join Rubros Rbr on Rbr.idRubro=Prd.idRubro

El resultado es el siguiente:

Vendedor             Cantidad               Rubro
-------------------- ---------------------- --------------------
Juan Perez           10                     Perfumes            
Maria Vazquez        5                      Perfumes            
Juan Perez           20                     Desodorantes        
Maria Vazquez        30                     Cremas              
Rocio Marquez        50                     Cremas              
Rocio Marquez        100                    Desodorantes        

(6 row(s) affected)

El objetivo es transformar los rubros en columnas y tener unas sola fila por cada vendedor, para esto utilizamos la sentencia PIVOT
La misma en este caso consta de 3 secciones:

1	Campos a mostrar
2	Consulta para obtener los datos
3	Conversion de filas en columnas

SELECT Vendedor,isnull([Perfumes],0) as Cremas ,isnull([Desodorantes],0) as Desodorantes, 
isnull([Cremas],0) as Perfumes
/*Select que define como se mostraran los tados y por que columna se agrupara (Vendedor)*/

FROM
(
Select 
cast(vnd.descripcion as char(20)) Vendedor, Cantidad , cast(rbr.descripcion as char(20))Rubro
From 
ventas vts inner join vendedores vnd
on vts.idvendedor=vnd.idvendedor
inner join Productos Prd 
on Prd.idproducto=vts.idProducto
Inner join Rubros Rbr on Rbr.idRubro=Prd.idRubro

) po

/*Origen de los Datos*/


PIVOT
(
SUM(cantidad) 
FOR Rubro IN 
    ([Cremas] , [Perfumes] , [DEsodorantes])
    ) AS PVT

/*TRansforma las filas en las columnas indicadas entre corchetes, agrupadas por cantidad, 
es decir obtiene las cantidades de cada rubro por vendedor*/

Vendedor             Cremas                 Desodorantes           Perfumes
-------------------- ---------------------- ---------------------- ----------------------
Juan Perez           10                     20                     0
Maria Vazquez        5                      0                      30
Rocio Marquez        0                      100                    50

(3 row(s) affected)

Las ventaja principal radica en las veces que se ejecuta la consulta, como vemos en la solución planteada para SQL 2000, se ejecuta una consulta para cada item a trasnsformar en columna, al contrario de lo que sucede con la sentencia PIVOT la cual la consulta de los datos se realiza una sola vez, a la vez que el query resuta menos complejo.

Conclusiones

La explotación de datos es parte fundamental en los sistemas de información. La transformacion de filas en columnas brinda información mas precisa al usuario y es muy util para tomar decisiones Genrenciales.
Tanto la utilización de la sentencia PIVOT como la solución de SQL2000 son alternatitvas para resolver requerimientos en la correcta visualizacion de datos.

Requisitos

Scripts

CREATE TABLE [dbo].[Productos](
	[IdProducto] [int] NULL,
	[Descripcion] [nvarchar](255) NULL,
	[IdRubro] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Rubros](
	[idRubro] [int] NULL,
	[Descripcion] [nvarchar](255)  NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Vendedores](
	[IDVendedor] [int] NULL,
	[Descripcion] [nvarchar](255)   NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Ventas](
	[IdVendedor] [int] NULL,
	[IdProducto] [int] NULL,
	[Cantidad] [int] NULL,
	[Fecha] [int] NULL
) ON [PRIMARY]


Insert into Rubros (idRubro,Descripcion) values (1 ,'Perfumes')
Insert into Rubros (idRubro,Descripcion) values (2 ,'Desodorantes')
Insert into Rubros (idRubro,Descripcion) values (3 ,'Cremas')

Insert into Vendedores (idVendedor,Descripcion) values (1 ,'Juan Perez')
Insert into Vendedores (idVendedor,Descripcion) values (2 ,'Maria Vazquez')
Insert into Vendedores (idVendedor,Descripcion) values (3 ,'Rocio Marquez')

Insert into Productos (idProducto,Descripcion,IdRubro) values (1 ,'Perfume Hombre',1 )
Insert into Productos (idProducto,Descripcion,IdRubro) values (2 ,'Perfume Mujer',1 )
Insert into Productos (idProducto,Descripcion,IdRubro) values (3 ,'Desodorante Hombre',2 )
Insert into Productos (idProducto,Descripcion,IdRubro) values (4 ,'Desodorante Mujer',2 )
Insert into Productos (idProducto,Descripcion,IdRubro) values (5 ,'Crema manos',3 )
Insert into Productos (idProducto,Descripcion,IdRubro) values (6 ,'Crema Cuerpo',3 )

Insert into Ventas (idVendedor,IdProducto,Cantidad,Fecha) values (1 ,1 ,10 ,'01/10/2005')
Insert into Ventas (idVendedor,IdProducto,Cantidad,Fecha) values (2 ,2 ,5  ,'01/10/2005')
Insert into Ventas (idVendedor,IdProducto,Cantidad,Fecha) values (1 ,4 ,20 ,'01/10/2005')
Insert into Ventas (idVendedor,IdProducto,Cantidad,Fecha) values (2 ,5 ,30 ,'01/10/2005')
Insert into Ventas (idVendedor,IdProducto,Cantidad,Fecha) values (3 ,6 ,50 ,'01/10/2005')
Insert into Ventas (idVendedor,IdProducto,Cantidad,Fecha) values (3 ,4 ,100,'01/10/2005')

Se Puede trabajar con los ejemplos mencionados una vez ejecutados los Scripts.