Pivot en SQL Server
Pivot y UnPivot son operadores relacionales que permiten mostrar datos de una consulta en un formato cambiado, tanto de columnas a filas o de filas a columnas.
Pivot cambia los valores únicos de una columna y muestra los resultados en varias columnas con cada uno de los valores únicos, pivot permite además realizar agregaciones. Unpivot realiza la acción contraria a lo que hace Pivot, cambia las columnas de una consulta en valores de una sola columna.
Para más información ver
Pivot SQL Server
Pivot y procedimientos almacenados
Unpivot en SQL Server
Crear Base de datos
Crear tablas
Insertar registros
Sintaxis de Pivot
SELECT ,
[ColumnaPivot1] As 'Alias',
[ColumnaPivot2] As 'Alias',
…
[ColumnaPivotN] As 'Alias'
FROM
Tabla | Consulta
AS
PIVOT
(Columna Agregada)
FOR
[Encabezados de columnas]
IN ( [ColumnaPivot1], [ColumnaPivot2], … [ColumnaPivotN])
) AS
[ORDER BY …]
Crear una base de datos, luego una tabla con datos de alumnos para luego realizar los diversos ejercicios
Creando la base de datos
Create database TrabajaPivotUnPivot
go
Abriendo la base de datos
use TrabajaPivotUnpivot
go
Creando una tabla con cursos, en año y la cantidad de alumnos capacitados.
Create table CursosAlumnos
(
CursosAlumnosCodigo nchar(5),
CursosAlumnosDescripcion nvarchar(30),
CursosAlumnosAnio int,
CursosAlumnosCantidadAlumnos int,
CursosAlumnosMontoIngresos Numeric(9,2),
constraint CursosAlumnosPK Primary key (CursosAlumnosCodigo)
)
go
Insertando los datos para luego hacer los ejercicios
insert into CursosAlumnos
values ('24026','SQL Server',2018,185,13000),
('16018','Aplicaciones Móviles',2019,90,60000),
('36963','SQL Server',2017,185,130000),
('15978','Aplicaciones Móviles',2018,50,40000),
('75321','Power BI',2018,88,250000),
('95174','Power BI',2019,250,850505),
('54685','SQL Server',2017,26,98000)
go
Listado de los registros de la tabla
Select * from dbo.CursosAlumnos
go
El resultado se muestra en la siguiente imagen
Ejercicios
Ejercicio 1
Mostrar los cursos y la cantidad de alumnos por año.
with TotalAlumnos As
(
select
[CursosAlumnosAnio], [CursosAlumnosDescripcion], [CursosAlumnosCantidadAlumnos]
from CursosAlumnos
)
select
[CursosAlumnosAnio] As 'Año',
IsNull([SQL Server],0) As 'SQL Server',
IsNull([Aplicaciones Móviles],0) As 'Aplicaciones Móviles',
IsNull([Power BI],0) 'Power BI'
from TotalAlumnos
pivot (Sum(CursosAlumnosCantidadAlumnos) for
CursosAlumnosDescripcion in ([SQL Server],[Aplicaciones Móviles],[Power BI]))
As PVT
go
El resultado se muestra en la siguiente imagen
Note que los años se muestran en la primera columna y luego por cada curso se suman la cantidad de alumnos.
Ejercicio 2
Mostrar los años y la cantidad de ingresos por curso.
with TotalAlumnos As
(
select
[CursosAlumnosAnio], [CursosAlumnosDescripcion], [CursosAlumnosMontoIngresos]
from CursosAlumnos
)
select
[CursosAlumnosAnio] As 'Año',
IsNull([SQL Server],0) As 'SQL Server',
IsNull([Aplicaciones Móviles],0) As 'Aplicaciones Móviles',
IsNull([Power BI],0) 'Power BI'
from TotalAlumnos
pivot (Sum([CursosAlumnosMontoIngresos]) for
CursosAlumnosDescripcion in ([SQL Server],[Aplicaciones Móviles],[Power BI]))
As PVT
go
El resultado se muestra en la siguiente imagen
Ejercicio 3
Mostrar los cursos y la cantidad de ingresos por año.
with TotalAlumnos As
(
select
[CursosAlumnosAnio], [CursosAlumnosDescripcion], [CursosAlumnosMontoIngresos]
from CursosAlumnos
)
select
[CursosAlumnosDescripcion] As 'Curso',
IsNull([2017],0) As '2017',
IsNull([2018],0) As '2018',
IsNull([2019],0) '2019'
from TotalAlumnos
pivot (Sum([CursosAlumnosMontoIngresos]) for
[CursosAlumnosAnio] in ([2017],[2018],[2019]))
As PVT
go
El resultado se muestra en la siguiente imagen
Ejercicio 4
Listado de productos por categoría y la cantidad de unidades vendidas por Trimestres
Se usará un procedimiento para hacer dinámica la categoría.
Usando la base de datos Northwind
use Northwind
go
with Ventas As
(
select
C.CategoryName,
Sum(D.Quantity) As 'Cantidad' , Datepart(QUARTER, O.OrderDate) As 'Trimestre'
from Products As P
join [Order Details] As D on P.ProductID = D.ProductID
join Orders As O on D.OrderID = O.OrderID
join Categories As C on P.CategoryID = C.CategoryID
where O.OrderDate between '01/01/1997' and '31/12/1997'
group by C.CategoryName, Datepart(QUARTER, O.OrderDate)
)
select
CategoryName As 'Categoria',
Isnull([1],0) As 'Primero',isNull([2],0) As 'Segundo',
IsNull([3],0) As 'Tercero',isNull([4],0) As 'Cuarto'
from Ventas
pivot
( sum(Cantidad) for Trimestre in ([1],[2],[3],[4]) ) As Trimestral
go
El resultado se muestra en la siguiente imagen
Procedimiento para hacerlo dinámico por año, donde el parámetro será el año.
Create or alter procedure spVentasUnidadesPorCategoriaAnualTrimestre (@Anio int)
As
Declare @FechaInicial Date = (select DATEFROMPARTS(@Anio, 1, 1))
Declare @FechaFinal Date = (select DATEFROMPARTS(@Anio, 12, 31));
with Ventas As
(
select
C.CategoryName,
Sum(D.Quantity) As 'Cantidad' , Datepart(QUARTER, O.OrderDate) As 'Trimestre'
from Products As P
join [Order Details] As D on P.ProductID = D.ProductID
join Orders As O on D.OrderID = O.OrderID
join Categories As C on P.CategoryID = C.CategoryID
where O.OrderDate between @FechaInicial and @FechaFinal
group by C.CategoryName, Datepart(QUARTER, O.OrderDate)
)
select
CategoryName As 'Categoria',
Isnull([1],0) As 'Primero',isNull([2],0) As 'Segundo',
IsNull([3],0) As 'Tercero',isNull([4],0) As 'Cuarto'
from Ventas
pivot
( sum(Cantidad) for Trimestre in ([1],[2],[3],[4]) ) As Trimestral
go
Ejecutar para el año 1997
Execute spVentasUnidadesPorCategoriaAnualTrimestre 1997
go
El resultado se muestra en la siguiente imagen
Ejecutar para el año 1998
Execute spVentasUnidadesPorCategoriaAnualTrimestre 1998
go
El resultado se muestra en la siguiente imagen
