Pivot y procedimientos almacenados

Usando Pivot y Procedimientos almacenados

Las operaciones con Pivot nos permitirá convertir los resultados de una consulta que se presentan en filas y mostrarlos en columnas. Pivot utiliza las funciones de agregado para presentar los datos en columnas. En este
artículo se presentan varios ejercicios usando el operador Pivot usando procedimientos almacenados para hacer las consultas dinámicas.

Los operadores relacionales PIVOT y UNPIVOT cambian una expresión con valores de tabla en otra tabla. PIVOT rota una expresión con valores de tabla convirtiendo los valores únicos de una columna en la expresión en varias columnas en la salida.
UNPIVOT realiza la operación opuesta a PIVOT al rotar columnas de una expresión con valores de tabla en valores de columna.

Para más información ver:
Funciones de agregado.
Pivot en SQL Server
Unpivot SQL Server
CTE en SQL Server
Agrupamiento en SQL Server
Joins en SQL Server
Procedimientos almacenados

Usando la base de datos Northwind
USE Northwind
go

Ejercicio 1

Mostrar las ventas de los productos por cada mes en un año determinado. El procedimiento almacenado recibe el año como parámetro.

Create or alter procedure spTotalVentasPorMesyAnio
(@Anio int)
As
with TotalVentas As
(
SELECT
P.ProductName As ‘Producto’,
MONTH(O.OrderDate) As ‘Mes’,
SUM(D.Quantity) As ‘Unidades’
from Products As P
join [Order Details] As D on P.ProductID = D.ProductID
join Orders As O on D.OrderID = O.OrderID
Where Year(O.OrderDate)= @Anio
GROUP BY P.ProductName, MONTH(O.OrderDate)
)
select
Producto, ISNULL([1],0) Ene, ISNULL([2],0) Feb,
ISNULL([3],0) Mar, ISNULL([4],0) Abr, ISNULL([5],0) May,
ISNULL([6],0) Jun, ISNULL([7],0) Jul, ISNULL([8],0) Ago,
ISNULL([9],0) Sep, ISNULL([10],0) Oct, ISNULL([11],0) Nov,
ISNULL([12],0) Dic
from TotalVentas
Pivot (SUM(Unidades)
for Mes in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]))
As Tabla
go

Ejecutar el procedimiento para ver las ventas del año 1997
Execute spTotalVentasPorMesyAnio 1997
go
El resultado se muestra en la siguiente imagen

Ejercicio 2

Listado de los empleados y las órdenes generadas por año.

Create or alter procedure spOrdenesPorAnioPorEmpleado
As
with TotalOrdenes As
(
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
Group by E.LastName + Space(1) + E.FirstName, YEAR(O.OrderDate) )
— El pivot
select * from TotalOrdenes
pivot (Sum(Órdenes) for Año in ([1996],[1997],[1998])) As Calculos
go

Ejecutar el procedimiento
Execute spOrdenesPorAnioPorEmpleado
go
El resultado se muestra en la siguiente imagen

Ejercicio 3

Listado de las ventas en unidades monetarias por categoría y por año.

Create or alter procedure spVentasTotalesCategoriaAnio
As
With VentasTotalesCategoria As
(
select
C.CategoryID As ‘Cód. Categoría’,
C.CategoryName As ‘Categoría’,
Year(O.OrderDate) As ‘Año’,
sum(D.Quantity * D.UnitPrice) As ‘Total’
from Categories As C
join Products As P on C.CategoryID = P.CategoryID
join [Order Details] As D on P.ProductID = D.ProductID
join Orders AS O on D.OrderID = O.OrderID
Group by C.CategoryID, C.CategoryName, Year(O.OrderDate)
)
select *
from VentasTotalesCategoria
pivot (sum(Total) for Año in ([1996],[1997],[1998])) As Totales
go

Ejecutar el procedimiento almacenado
Execute spVentasTotalesCategoriaAnio
go
El resultado se muestra en la siguiente imagen

Ejercicio 4

Total de ventas de un producto por año.

Create or alter procedure spVentasPorProductoPorCategoriaAnio
(@CodigoProducto Int)
As
With VentasTotalesProductoCategoria As
(
select
C.CategoryID As ‘Cód. Categoría’,
C.CategoryName As ‘Categoría’,
P.ProductName As ‘Producto’,
Year(O.OrderDate) As ‘Año’,
sum(D.Quantity * D.UnitPrice) As ‘Total’
from Categories As C
join Products As P on C.CategoryID = P.CategoryID
join [Order Details] As D on P.ProductID = D.ProductID
join Orders AS O on D.OrderID = O.OrderID
where P.ProductID = @CodigoProducto
Group by C.CategoryID, C.CategoryName,
Year(O.OrderDate), P.ProductName
)
select *
from VentasTotalesProductoCategoria
pivot (sum(Total) for Año in ([1996],[1997],[1998])) As Totales
go

Ejecutar para el producto con código 5
Execute spVentasPorProductoPorCategoriaAnio 5
go
El resultado se muestra en la siguiente imagen

Ejercicio 5

Total vendido por empleado por mes en un determinado año.

Create or alter procedure spTotalVentasPorEmpleadoMesyAnio
(@Anio int)
As
with TotalVentasEmpleado As
(
SELECT
Empleado = E.LastName + Space(1) + E.FirstName,
MONTH(O.OrderDate) As ‘Mes’,
SUM(D.Quantity * D.UnitPrice) As ‘Total’
from Employees As E
join Orders As O on E.EmployeeID= O.EmployeeID
join [Order Details] As D on O.OrderID= D.OrderID
Where Year(O.OrderDate)= @Anio
GROUP BY E.LastName + Space(1) + E.FirstName, MONTH(O.OrderDate)
)
select
Empleado, ISNULL([1],0) Ene, ISNULL([2],0) Feb,
ISNULL([3],0) Mar, ISNULL([4],0) Abr, ISNULL([5],0) May,
ISNULL([6],0) Jun, ISNULL([7],0) Jul, ISNULL([8],0) Ago,
ISNULL([9],0) Sep, ISNULL([10],0) Oct, ISNULL([11],0) Nov,
ISNULL([12],0) Dic
from TotalVentasEmpleado
Pivot (SUM(Total)
for Mes in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]))
As Ventas
go

Ejecutar el procedimiento almacenado para el año 1997
Execute spTotalVentasPorEmpleadoMesyAnio 1997
go
El resultado se muestra en la siguiente imagen

Obtener los valores de un campo encerrados entre corchetes

Para obtener los valores de un campo encerrados entre corchetes se va a crear una variable para almacenar en esta los valores.

Ejercicio 6

Obtener las descripciones de las categorías en una variable tipo cadena. Esta cadena de caracteres se usará junto con el operador Pivot para realizar la comparación usando el operador In.
DECLARE @Categorias nvarchar(MAX) = »
SELECT @Categorias += QUOTENAME(CategoryName) + ‘,’
FROM Categories Order by CategoryName
Set @Categorias = Left(@Categorias, LEN(@Categorias) – 1);
select @Categorias
go
El resultado se muestra en la siguiente imagen

Ejercicio 7

Obtener las descripciones de los productos de la categoria 6
DECLARE @ProductosCategoria6 nvarchar(MAX) = »
SELECT @ProductosCategoria6 += QUOTENAME(ProductName) + ‘,’
FROM Products where CategoryID = 6
Order by ProductName
Set @ProductosCategoria6 = Left(@ProductosCategoria6, LEN(@ProductosCategoria6) – 1)
Print @ProductosCategoria6
go
El resultado se muestra en la siguiente imagen

Ejercicio 8

Las ventas de los productos de una categoría en cada año. Para el ejemplo se va a usar los datos de la categoría 6
with VentasProductoAnio As
(
select
P.ProductName As ‘Producto’,
Year(O.OrderDate) As ‘Año’,
sum(D.Quantity) As ‘Unidades’
from Products As P
join [Order Details] As D on P.ProductID = D.ProductID
join Orders As O on D.OrderID = O.OrderID
where P.CategoryID = 6
Group by P.ProductName, Year(O.OrderDate)
)
select * from VentasProductoAnio
pivot (sum(Unidades) for Año in ([1996],[1997],[1998]) )
As Totales
go

Productos de la categoría 6

El procedimiento almacenado para cualquier categoría. Este recibe el año como parámetro.
Create or alter procedure spVentasProductosCategoriaAnio
(
@CodigoCategoria int
)
As
with VentasProductoAnio As
(
select
P.ProductName As ‘Producto’,
Year(O.OrderDate) As ‘Año’,
sum(D.Quantity) As ‘Unidades’
from Products As P
join [Order Details] As D on P.ProductID = D.ProductID
join Orders As O on D.OrderID = O.OrderID
where P.CategoryID = @CodigoCategoria
Group by P.ProductName, Year(O.OrderDate)
)
select * from VentasProductoAnio
pivot (sum(Unidades) for Año in ([1996],[1997],[1998]) )
As Totales
go

Ejecutar el SP para los productos de la categoría 6
Execute spVentasProductosCategoriaAnio 6
go

El resultado es similar al de la imagen anterior.

Ahora se va a mostrar los productos como encabezados de columna

Para generar la cadena con los productos sólo de la categoría 6 (como ejemplo) se usa el siguiente código.
— Generar los productos
DECLARE @ProductosCategoria nvarchar(MAX) = »
SELECT @ProductosCategoria += QUOTENAME(ProductName) + ‘,’
FROM Products
where CategoryID = 6
Order by ProductName
Set @ProductosCategoria = Left(@ProductosCategoria, LEN(@ProductosCategoria) – 1)
Declare @Productos nvarchar(MAX)= (select (@ProductosCategoria))
Select @Productos
go

El código anterior genera esta cadena.
[Alice Mutton],[Mishi Kobe Niku],[Pâté chinois],[Perth Pasties],[Thüringer Rostbratwurst],[Tourtière]

Si se reemplaza la cadena en el operador Pivot
Select * from
(select
P.ProductName As Producto,
Year(O.OrderDate) As Año,
sum(D.Quantity) As Unidades
from Products As P
join [Order Details] As D on P.ProductID = D.ProductID
join Orders As O on D.OrderID = O.OrderID
where P.CategoryID = 6
Group by P.ProductName, Year(O.OrderDate)
) As Tabla
pivot (sum(Unidades) for Producto in
([Alice Mutton],[Mishi Kobe Niku],[Pâté chinois],[Perth Pasties],[Thüringer Rostbratwurst],[Tourtière]) )
As Totales
go
Se muestran los productos de la categoría 6.

Ahora usando la cadena generada en una instrucción SQL

Declare @Instruccion nvarchar(max)
DECLARE @ProductosCategoria nvarchar(MAX) = »
SELECT @ProductosCategoria += QUOTENAME(ProductName) + ‘,’
FROM Products
where CategoryID = 6
Order by ProductName
Set @ProductosCategoria = Left(@ProductosCategoria, LEN(@ProductosCategoria) – 1)
Declare @Productos nvarchar(MAX)= (select (@ProductosCategoria))
Set @Instruccion =
‘Select * from
(select
P.ProductName As Producto,
Year(O.OrderDate) As Año,
sum(D.Quantity) As Unidades
from Products As P
join [Order Details] As D on P.ProductID = D.ProductID
join Orders As O on D.OrderID = O.OrderID
where P.CategoryID = 6
Group by P.ProductName, Year(O.OrderDate)
) As Tabla
pivot (sum(Unidades) for Producto in (‘ + @Productos + ‘) )
As Totales’;
Execute (@Instruccion)
go

Creando un SP para hacerlo dinámico, para los productos de cualquier categoría.
Create or alter procedure spVentasProductosCategoriaAnio
(
@CodigoCategoria int
)
As
Declare @Instruccion nvarchar(max)
DECLARE @ProductosCategoria nvarchar(MAX) = »
SELECT @ProductosCategoria += QUOTENAME(ProductName) + ‘,’
FROM Products
where CategoryID = @CodigoCategoria
Order by ProductName
Set @ProductosCategoria = Left(@ProductosCategoria, LEN(@ProductosCategoria) – 1)
Declare @Productos nvarchar(MAX)= (select (@ProductosCategoria))
Set @Instruccion =
‘Select * from
(select
P.ProductName As Producto,
Year(O.OrderDate) As Año,
sum(D.Quantity) As Unidades
from Products As P
join [Order Details] As D on P.ProductID = D.ProductID
join Orders As O on D.OrderID = O.OrderID
where P.CategoryID = ‘ + Str(@CodigoCategoria) + ‘
Group by P.ProductName, Year(O.OrderDate)
) As Tabla
pivot (sum(Unidades) for Producto in (‘ + @Productos + ‘) )
As Totales’;
Execute (@Instruccion)
go

Las ventas de los productos de la categoría 5
Execute spVentasProductosCategoriaAnio 5
go