Pivot

Como usar Pivot en SQL Server

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.

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

Ejemplos

Ejercicio 01

En el siguiente ejemplo se va a crear una base de datos, en ella una tabla Ventas, insertar registros con los datos de tres clientes de los cuales se han registrado varias ventas. Luego se mostrará los resultados en columnas de acuerdo al artículo vendido.

Create database PruebaPivot
go
use PruebaPivot
go
Create table Ventas
(
VentasCliente nvarchar(50),
VentasProducto nvarchar(50),
VentasCantidad Numeric(9,2)
)
go
Esta tabla puede ser generada por una consulta, revisar la opción Into en Consultas en Consultas Opciones

Insertar datos

Insert into Ventas values (‘Carla’,’Teclado’,10),(‘Pedro’,’Monitor’,12),(‘Carla’,’Monitor’,6),
(‘Carla’,’Mouse’,24),(‘Pedro’,’Teclado’,16),(‘José’,’Monitor’,22),(‘José’,’Teclado’,3),
(‘Carla’,’Teclado’,42),(‘Pedro’,’Mouse’,34),(‘José’,’Mouse’,10),(‘Pedro’,’Teclado’,10)
go

Haciendo la consulta de los datos de ventas, se presenta ordenado por cliente para mejor entendimiento
select * from Ventas order by VentasCliente
go
Note que Carla ha comprado diferentes productos, así como también José y Pedro. En la imagen se resalta la cantidad total comprada de Teclados por Carla.

Pivot permitirá mostrar por cada Cliente, cuantos Teclados, Monitores y Mouses compraron.

Select * from Ventas
Pivot (Sum(VentasCantidad) for
VentasProducto in ([Monitor],[Mouse],[Teclado]))
As TablaPivot
go



 

Usando la base de datos Northwind

use Northwind
go

Ejercicio 02

— Los clientes y las compras por año.

select C.CompanyName As ‘Cliente’,
DATEPART(YY,O.OrderDate) As ‘Año’,
Sum(Od.UnitPrice * OD.Quantity) As ‘Importe’
from Customers as C
join Orders as O on C.CustomerID = O.CustomerID
join [Order Details] as OD on O.OrderID = OD.OrderID
group by C.CompanyName, DATEPART(YY,O.OrderDate)
go

— Para hacer mas sencilla la lectura de la consulta, se utilizará una Expresión de tabla común
— Common Table Expression, recuerde que se utiliza en el mismo lote.

with Ventas As (
select C.CompanyName As ‘Cliente’,
DATEPART(YY,O.OrderDate) As ‘Año’,
Sum(Od.UnitPrice * OD.Quantity) As ‘Importe’
from Customers as C
join Orders as O on C.CustomerID = O.CustomerID
join [Order Details] as OD on O.OrderID = OD.OrderID
group by C.CompanyName, DATEPART(YY,O.OrderDate))
select * from Ventas
pivot (Sum(Importe) for Año in ([1996],[1997],[1998])) PivotTable
— Columnas para los años 1996, 1997 y 1998 Pivot para los años

 

Ejercicio 03

— Empleados y las órdenes generadas por año.

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)
go

— Se sabe que los años son 1996, 1997 y 1998
— Se desea por cada empleado y por año las cantidades de órdenes

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