UnPivot SQL Server

Unpivot en SQL Server

Los operadores PIVOT y UNPIVOT de la instrucción Select permiten cambiar una expresión con valores de tabla en otra tabla donde las columnas de la tabla origen se transponen en la tabla destino.

PIVOT gira una expresión con valores de tabla al convertir los valores únicos de una columna en la expresión en varias columnas en la salida. PIVOT ejecuta agregaciones donde se requieren en los valores de columna restantes que se desean en la salida final.
UNPIVOT realiza la operación opuesta a PIVOT girando las columnas de una expresión con valores de tabla en valores de columna.

Sintaxis Select incluyendo Unpivot
SELECT …
FROM …
UNPIVOT (
FOR IN ( ) )

Ejemplos

Usando el operador Pivot

Ejercicio 1

Usando Northwind vamos a presentar los empleados y la cantidad de productos vendidos por categoría.
Ver Funciones nuevas en SQL Server 2017
Ver Consultas de varias tablas con Join
Ver Pivot en SQL Server

use Northwind
go

Select Empleado = CONCAT_WS(‘ ‘, E.FirstName, E.LastName),
C.CategoryName As ‘Categoría’,
Sum(D.Quantity) As ‘Cantidad’,
Year(O.OrderDate) As ‘Año’
from Employees As E
join Orders As O on E.EmployeeID = O.EmployeeID
join [Order Details] As D on O.OrderID = D.OrderID
join Products As P on D.ProductID = P.ProductID
join Categories As C on P.CategoryID = C.CategoryID
Group by CONCAT_WS(‘ ‘, E.FirstName, E.LastName), C.CategoryName, Year(O.OrderDate)
order by Empleado, Cantidad desc
go
El listado de los empleados es el siguiente.

Generar para facilidad una tabla con las ventas anteriores. Puede usar CTE
Ver Generar una tabla con Into

Drop table if exists VentasEmpleadosYearCategoria
Select Empleado = CONCAT_WS(‘ ‘, E.FirstName, E.LastName),
C.CategoryName As ‘Categoría’,
Sum(D.Quantity) As ‘Cantidad’,
Year(O.OrderDate) As ‘Año’
into VentasEmpleadosYearCategoria
from Employees As E
join Orders As O on E.EmployeeID = O.EmployeeID
join [Order Details] As D on O.OrderID = D.OrderID
join Products As P on D.ProductID = P.ProductID
join Categories As C on P.CategoryID = C.CategoryID
Group by CONCAT_WS(‘ ‘, E.FirstName, E.LastName), C.CategoryName, Year(O.OrderDate)
order by Empleado, Cantidad desc
go

Mostrar las ventas de cada empleado por Año.
select *
from VentasEmpleadosYearCategoria
pivot (Sum(Cantidad) for Año in ([1996],[1997],[1998])) As Totales
go

Mostrar ahora de las categorías Beverages, Condiments y Confections
select *
from VentasEmpleadosYearCategoria
pivot (Sum(Cantidad) for Categoría in ([Beverages], [Condiments],[Confections])) As Totales
go

Ejercicio 2

Usando el operador UnPivot

Listado de clientes y sus compras por año. Para las compras se va a usar una FDU, puede ser también una subconsulta

La FDU para el cálculo de las compras de cada cliente por Año. (Ver Funciones definidas por el usuario)
Create function dbo.fduCompraClientesPorYear (@Anio int, @CodigoCliente nchar(5))
returns Numeric(9,2)
As
Begin
Declare @TotalCompra Numeric(9,2)
Set @TotalCompra =
(
Select Sum((D.Quantity * D.UnitPrice) * (1- D.Discount))
from Customers As C
join Orders As O on C.CustomerID = O.CustomerID
join [Order Details] As D on O.OrderID = D.OrderID
where Year(O.OrderDate) = @Anio
and C.CustomerID = @CodigoCliente
)
Return @TotalCompra
End
go

La instrucción para el total de compras por cliente por año.

select C.CompanyName As ‘Cliente’,
isnull(dbo.fduCompraClientesPorYear(1996, C.CustomerID),0) As ‘1996’,
isnull(dbo.fduCompraClientesPorYear(1997, C.CustomerID),0) As ‘1997’,
isnull(dbo.fduCompraClientesPorYear(1998, C.CustomerID),0) As ‘1998’
from Customers As C
go

Generando una tabla (no es necesario pero es mas sencillo)
drop table if exists ComprasPorClientePorAnio
select C.CompanyName As ‘Cliente’,
isnull(dbo.fduCompraClientesPorYear(1996, C.CustomerID),0) As ‘1996’,
isnull(dbo.fduCompraClientesPorYear(1997, C.CustomerID),0) As ‘1997’,
isnull(dbo.fduCompraClientesPorYear(1998, C.CustomerID),0) As ‘1998’
into ComprasPorClientePorAnio
from Customers As C
go

Ahora usando Unpivot

Se va a mostrar los clientes y luego los años y las cantidades.
Select Cliente, Años, Totales
from ComprasPorClientePorAnio As C
unpivot (Totales for Años in ([1996], [1997], [1998])) As Ventas
go

Resultado operador UnPivot

La imagen siguiente muestra los resultados de la instrucción Select y del Unpivot. Note el cliente resaltado.

Dos conjuntos de resultados, el de la derecha usando el operador Unpivot.

Ejercicio 3

Usando el operador UnPivot

Listado de Empleados y sus ventas por año. Para las Ventas se va a usar una subconsulta
Generando la tabla ComprasPorEmpleadoPorAnio.
Ver Subconsultas
Ver Generar una tabla con Into

drop table if exists ComprasPorEmpleadoPorAnio
select Empleado = CONCAT_WS(‘ ‘, Em.FirstName, Em.LastName),
Format(isnull((select Sum((D.Quantity * D.UnitPrice) * (1- D.Discount))
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) = 1996
and E.EmployeeID= Em.EmployeeID),0),’###,##0.00′) As ‘1996’,
Format(isnull((select Sum((D.Quantity * D.UnitPrice) * (1- D.Discount))
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) = 1997
and E.EmployeeID= Em.EmployeeID ),0),’###,##0.00′) As ‘1997’,
Format(isnull((select Sum((D.Quantity * D.UnitPrice) * (1- D.Discount))
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) = 1998
and E.EmployeeID= Em.EmployeeID ),0),’###,##0.00′) As ‘1998’
into ComprasPorEmpleadoPorAnio
from Employees As Em
go

La tabla de ComprasPorEmpleadoPorAnio
select * from ComprasPorEmpleadoPorAnio
go

Ahora, usando Unpivot presentar los clientes y de cada cliente los años y sus totales.

select Empleado, Año, Totales
from ComprasPorEmpleadoPorAnio
unpivot (Totales for Año in ([1996], [1997], [1998])) As Totales
go

Puede notar el resultado del operador Unpivot

Dos conjuntos de resultados, el de la derecha usando el operador Unpivot.