Pivot y Tabla de referencia cruzada

Pivot y tablas de referencia cruzada

Las operaciones con la cláusula Pivot nos permite convertir los resultados de una consulta que se muestra en filas y transponer los resultados en columnas. Pivot utiliza las funciones de agregado para presentar los datos en columnas.

Para información de las funciones de agregado Ver Funciones de agregado.

En este artículo vamos a mostrar varios ejercicios usando la cláusula Pivot y ver los resultados de filas en columnas.

Usando la base de datos Northwind
use Northwind
go

Ejercicio 1

Listado de productos de las categorias 1,2 y 3 con la cantidad de unidades vendidas por mes en el año 1997

Antes de usar Pivot y hace la consulta con referencia cruzada, se va a crear la consulta.

set dateformat dmy
go
select 
	P.ProductID As 'Cód. Producto',
	P.ProductName As 'Descripción',
	P.CategoryID As 'Cód. Categoría',
	DATENAME(MONTH, O.OrderDate) As 'Mes' ,
	sum(D.Quantity) As 'Cantidad Vendida'
	from dbo.[Order Details] As D 
	join dbo.Products As P on D.ProductID = P.ProductID
	join dbo.Orders As O on D.OrderID = O.OrderID
	where P.CategoryID in (1,2,3)
		and O.OrderDate between '01/01/1997' and '31/12/1997'
	group by P.ProductID, P.ProductName, P.CategoryID, DATENAME(MONTH, O.OrderDate)
go

La imagen muestra el resultado

En el listado anterior se puede observar nombres de los meses, la cláusula Pivot y la referencia cruzada agrupará por mes y obtendrá los totales.

Ordenamos la consulta anterior por Id del Producto para comprobar que los resultados son correctos, nos fijaremos en la cantidad del producto con ID = 1

select 
	P.ProductID As 'Cód. Producto',
	P.ProductName As 'Descripción',
	P.CategoryID As 'Cód. Categoría',
	DATENAME(MONTH, O.OrderDate) As 'Mes' ,
	sum(D.Quantity) As 'Cantidad Vendida'
	from dbo.[Order Details] As D 
	join dbo.Products As P on D.ProductID = P.ProductID
	join dbo.Orders As O on D.OrderID = O.OrderID
	where P.CategoryID in (1,2,3)
		and O.OrderDate between '01/01/1997' and '31/12/1997'
	group by P.ProductID, P.ProductName, P.CategoryID, DATENAME(MONTH, O.OrderDate)
	order by P.ProductID
go

La imagen muestra el resultado.

La suma de los valores seleccionados de las cantidades vendidas en los meses para el producto 1 es: 304

Podemos comprobar la cantidad de unidades vendidas del producto 1 en 1997
select
sum(d.Quantity)
from dbo.[Order Details] As D
join dbo.Orders As O on D.OrderID = O.OrderID
where D.ProductID = 1 and O.OrderDate between ’01/01/1997′ and ’31/12/1997′
go

Formando la consulta con Pivot como referencia cruzada

select 
	*
	from
	(
	select 
	P.ProductID As 'Cód. Producto',
	P.ProductName As 'Descripción',
	P.CategoryID As 'Cód. Categoría',
	DATENAME(MONTH, O.OrderDate) As 'Mes' ,
	sum(D.Quantity) As 'Cantidad'
	from dbo.[Order Details] As D 
	join dbo.Products As P on D.ProductID = P.ProductID
	join dbo.Orders As O on D.OrderID = O.OrderID
	where P.CategoryID in (1,2,3)
		and O.OrderDate between '01/01/1997' and '31/12/1997'
	group by P.ProductID, P.ProductName, P.CategoryID, DATENAME(MONTH, O.OrderDate)
	) As T
	pivot (Sum(T.Cantidad) for T.Mes in 
		([Enero],[Febrero],[Marzo],[Abril],[Mayo],[Junio],
		 [Julio],[Agosto],[Septiembre],[Octubre],[Noviembre],[Diciembre])) PVT
go

La imagen muestra el resultado

Eliminando los valores Null de la consulta.

Para eliminar los valores Null que indican que en ese mes no se han vendido unidades del producto, usaremos Case con la expresión lógica de comprobar si el valor es Null.

select 
	[Cód. Producto],
	Descripción,
	[Cód. Categoría],
	Case when Enero is not null then Enero Else 0 End As Enero,
	Case when Febrero is not null then Febrero Else 0 End As Febrero,
	Case when Marzo is not null then Marzo Else 0 End As Marzo,
	Case when Abril is not null then Abril Else 0 End As Abril,
	Case when Mayo is not null then Mayo Else 0 End As Mayo,
	Case when Junio is not null then Junio Else 0 End As Junio,
	Case when Julio is not null then Julio Else 0 End As Julio,
	Case when Agosto is not null then Agosto Else 0 End As Agosto,
	Case when Septiembre is not null then Septiembre Else 0 End As Septiembre,
	Case when Octubre is not null then Octubre Else 0 End As Octubre,
	Case when Noviembre is not null then Noviembre Else 0 End As Noviembre,
	Case when Diciembre is not null then Diciembre Else 0 End As Diciembre
	from
	(
	select 
	P.ProductID As 'Cód. Producto',
	P.ProductName As 'Descripción',
	P.CategoryID As 'Cód. Categoría',
	DATENAME(MONTH, O.OrderDate) As 'Mes' ,
	sum(D.Quantity) As 'Cantidad'
	from dbo.[Order Details] As D 
	join dbo.Products As P on D.ProductID = P.ProductID
	join dbo.Orders As O on D.OrderID = O.OrderID
	where P.CategoryID in (1,2,3)
		and O.OrderDate between '01/01/1997' and '31/12/1997'
	group by P.ProductID, P.ProductName, P.CategoryID, DATENAME(MONTH, O.OrderDate)
	) As T
	pivot (Sum(T.Cantidad) for T.Mes in 
		([Enero],[Febrero],[Marzo],[Abril],[Mayo],[Junio],
		 [Julio],[Agosto],[Septiembre],[Octubre],[Noviembre],[Diciembre])) PVT
go

La imagen muestra el resultado

Ejercicio 2

Crear un listado de la cantidad de órdenes atendidas por trimestres del año 1997. Las empresas de envío o Couriers son los Shippers y las órdenes atendidas son las que en el campo ShippedDate no es null.

select 
	[Cód. Courier],
	Courier,
	[1] as 'Primer Trimestre',
	[2] as 'Segundo Trimestre',
	[3] as 'Tercer Trimestre',
	[4] as 'Cuarto Trimestre'
	from
	(
	select
		S.ShipperID As 'Cód. Courier',
		S.CompanyName As 'Courier',
		COUNT(O.OrderID) As 'Órdenes',
		DATENAME(QUARTER, O.OrderDate) As 'Trimestre'
		from dbo.Orders As O
		join dbo.Shippers As S on O.ShipVia = S.ShipperID
		where O.ShippedDate is not null 
		and O.OrderDate between '01/01/1997' and '31/12/1997'	
		group by S.ShipperID, S.CompanyName, DATENAME(QUARTER, O.OrderDate)
	) As T
	Pivot (sum(T.Órdenes) for T.Trimestre in ([1],[2],[3],[4])) As PVT
go

La imagen muestra el resultado

Ejercicio 3

Listado de los empleados y la cantidad de órdenes atendidas que demoraron menos de 10 días en atender.

select 
	* 
	from
	(
	Select 
		Empleado = E.LastName + Space(1) + E.FirstName, YEAR(O.OrderDate) As 'Año',
		COUNT(O.OrderID) As 'Órdenes'
		from Employees As E
		join Orders As O on E.EmployeeID = O.EmployeeID
		where DATEDIFF(DAY,O.OrderDate, O.ShippedDate) <10
			and O.ShippedDate is not null
		Group by E.LastName + Space(1) + E.FirstName, YEAR(O.OrderDate) 
	) As T
	pivot (Sum(Órdenes) for Año in ([1996],[1997],[1998])) As Calculos
go

La imagen muestra el resultado

Ejercicio 4

Listado de clientes de un determinado país, la cantidad de órdenes y el monto total de estas agrupados por año. Se va a crear una función definida por el usuario (Ver Funciones definidas por el usuario) para el cálculo del total de la orden y crear un procedimiento almacenado que reciba el país para el listado de los clientes

La función definida por el usuario para el total de la orden
Create or alter function dbo.fduTotalMontoOrden (@IdOrden int)
returns Numeric(19,2)
As
Begin
Declare @Total Numeric(19,2)
set @Total =
(select Sum((Od.UnitPrice * OD.Quantity)*(1-Od.Discount))
from dbo.[Order Details] As Od where Od.OrderID = @IdOrden )
return @Total
End
go

El listado de los clientes de Argentina, país que se toma como ejemplo.
select
C.CompanyName As ‘Cliente’,
DATEPART(YY,O.OrderDate) As ‘Año’,
sum(dbo.fduTotalMontoOrden(O.OrderID)) As ‘Importe’
from dbo.Customers as C
join dbo.Orders as O on C.CustomerID = O.CustomerID
where C.Country = ‘Argentina’
group by C.CompanyName, DATEPART(YY,O.OrderDate)
go
La imagen muestra el resultado, realmente son tres clientes y se notan las compras en los diferentes años.

Creando la consulta con la cláusula Pivot como referencia cruzada

select
	R.Cliente,
	Case when R.[1996] is not null then R.[1996] else 0 end As [1996],
	Case when R.[1997] is not null then R.[1997] else 0 end As [1997],
	Case when R.[1998] is not null then R.[1998] else 0 end As [1998]
	from
	(
	select 
		C.CompanyName As 'Cliente',
		DATEPART(YY,O.OrderDate) As 'Año',
		sum(dbo.fduTotalMontoOrden(O.OrderID)) As 'Importe'
		from dbo.Customers as C
		join dbo.Orders as O on C.CustomerID = O.CustomerID
		where C.Country = 'Argentina'
		group by C.CompanyName, DATEPART(YY,O.OrderDate)
	) As T
	pivot (sum(T.Importe) for Año in ([1996],[1997],[1998])) As R
go

La imagen muestra el resultado

Creando el Procedimiento almacenado, este procedimiento recibe el nombre del país como parámetro.

Create or alter procedure dbo.spClientesPorPaisMontoCompras
	(
	@Pais nvarchar(15)
	)
	As
	select
		R.Cliente,
		Case when R.[1996] is not null then R.[1996] else 0 end As [1996],
		Case when R.[1997] is not null then R.[1997] else 0 end As [1997],
		Case when R.[1998] is not null then R.[1998] else 0 end As [1998]
		from
		(
		select 
			C.CompanyName As 'Cliente',
			DATEPART(YY,O.OrderDate) As 'Año',
			sum(dbo.fduTotalMontoOrden(O.OrderID)) As 'Importe'
			from dbo.Customers as C
			join dbo.Orders as O on C.CustomerID = O.CustomerID
			where C.Country = @Pais
			group by C.CompanyName, DATEPART(YY,O.OrderDate)
		) As T
		pivot (sum(T.Importe) for Año in ([1996],[1997],[1998])) As R
go

Mostrando los clientes de Argentina
Execute dbo.spClientesPorPaisMontoCompras ‘Argentina’
go

Los clientes de Mexico
Execute dbo.spClientesPorPaisMontoCompras ‘Mexico’
go
La imagen muestra el resultado

Listado de los clientes de Mexico
select CustomerID, CompanyName, Country
from dbo.Customers where Country = ‘Mexico’
go
La imagen muestra el resultado