PIVOT
Proporciona un mecanismo fcil de SQL SERVER para transformar filas en columnas.
Para ver el uso de PIVOT crearemos algunas tablas temporales.
Crear la siguiente tabla temporal:
-- Creando tabla temporal #Ingresos
CREATE TABLE #Ingresos
(Curso VARCHAR(50), Ao INT, Ganancias MONEY)
GO
-- Agregando algunos registros
INSERT INTO #Ingresos VALUES
('.NET',2014,10000),
('Java',2014,20000),
('.NET',2014,5000),
('.NET',2015,48000),
('Java',2015,30000);
GO
Ejemplo 1:
En este ejemplo los datos de la tabla #Ingresos se hace pivotar de modo que el curso se convierte en
los encabezados de columna.
Tabla Temporal #Ingresos
PIVOT sobre columna Curso Resultado del PIVOT con el
campo Curso como columnas
SELECT * FROM #Ingresos
PIVOT (SUM(Ganancias)
FOR Curso IN ([.NET],
Java)) AS PVT
Script Pivot sobre la tabla #Ingresos, donde los distintos valores del campo Curso son transformados
en columnas.
SELECT * FROM #Ingresos
PIVOT(SUM(Ganancias)
FOR Curso IN ([.NET], Java)) AS PVT
Ejemplo 2:
En este ejemplo los datos de tabla #Ingresos se hace pivotar de modo que el ao se convierte en los
encabezados de columna.
Tabla Temporal #Ingresos
PIVOT sobre columna Ao Resultado del PIVOT con el
campo Ao como columnas
SELECT *
FROM #Ingresos
PIVOT (SUM(Ganancias)
FOR Ao IN
([2014],[2015])) AS
PVT
Ejemplo 3:
La transformacin de los datos de la tabla de Ventas con una publicacin trimestral los datos de
ventas agregadas con Trimestre (Quarters) como las columnas del conjunto de resultados.
Primero creamos la tabla temporal con 500 registros:
-- Crea Tabla Temporal
CREATE TABLE #Ventas
(IdVenta INT IDENTITY(1,1), FechaVta Date)
GO
-- Rellenar 500 registros de Ventas con
-- Random de 0-500 dias como fecha de venta
INSERT INTO #Ventas(FechaVta)
VALUES(DATEADD(dd, - CONVERT(INT, (1000+1)*RAND()),GETDATE()));
GO 500
Observamos en una consulta por agrupacin La Obtencin las ventas Trimestrales:
SELECT DATEPART(YEAR,FechaVta) [Ao],
DATEPART(QUARTER,FechaVta) [Trimestre], COUNT(*) [NroVentas]
FROM #Ventas
GROUP BY DATEPART(YEAR,FechaVta),DATEPART(QUARTER,FechaVta)
ORDER BY 1,2
Como puede observar hemos obtenido el nmero
total de ventas realizadas en cada trimestre de cada
ao.
No olvide que si Ud. Genera la tabla de nuevo los
resultados no sern los mismos del ejemplo dado que
son datos aleatorios.
Ahora aplicaremos PIVOT para obtener el Resumen en forma Horizontal:
SELECT Ao, [1] AS Trim1, [2] AS Trim2, [3] AS Trim3,
[4] AS Trim4
FROM (
SELECT YEAR(FechaVta) AS Ao, DATEPART(QUARTER, FechaVta) AS Trim,
COUNT(*) AS NroVentas FROM #Ventas
GROUP BY YEAR(FechaVta), DATEPART(QUARTER,FechaVta)
) As SCR
PIVOT(SUM(NroVentas)
FOR Trim IN ([1],[2],[3],[4])) AS PVT
Ahora si deseamos la cantidad de ventas por meses:
SELECT *
FROM (
SELECT YEAR(FechaVta) AS Ao, DATENAME(Month, FechaVta) AS Mes,
COUNT(*) AS NroVentas FROM #Ventas
GROUP BY YEAR(FechaVta), DATENAME(Month, FechaVta)
) As SCR
PIVOT(SUM(NroVentas)
FOR Mes IN (Enero,Febrero,Marzo,Abril,Mayo,Junio,Julio,Agosto,
Septiembre,Octubre,Noviembre,Diciembre)) AS PVT
PIVOT DINAMICOS
A los anteriores ejemplos se le aplico PIVOT esttico. Es decir los nombres de las
columnas en el PIVOT se han asignado mediante nombres estticos. Ahora vamos a ver
cmo podramos crear PIVOT con nombres de columnas generados dinmicamente.
Tomando como ejemplo la tabla temporal #Ingresos
Si queremos obtener los
ingresos por ao y por
curso aplicamos el
siguiente PIVOT:
SELECT * FROM #Ingresos
PIVOT(SUM(Ganancias)
FOR Curso IN ([.NET],
Java)) AS PVT
En este ejemplo las columnas [.Net] y [Java] son columnas Estticas.
Si agregamos ms registros con las ganancias de otros cursos esta consulta PIVOT no
reflejara los nuevos datos a no ser que agreguemos las columnas necesarias.
Para evitar el estar agregando las columnas del PIVOT podramos generar estas columnas
dinmicamente.
INSERT INTO #Ingresos VALUES
('SQL Server',2014,15000),
('Phyton',2014,25000),
('SQL Server',2014,25000),
('SQL Server',2015,50000),
('Phyton',2015,40000);
SELECT * FROM #Ingresos
PIVOT(SUM(Ganancias)
FOR Curso IN ([.NET], Java)) AS PVT
Como tener una consulta con PIVOT Dinmico:
DECLARE @PivotDinamico AS NVARCHAR(MAX)
DECLARE @Columnas AS NVARCHAR(MAX)
-- Obtiene las columnas para la Columna Pivot
SELECT @Columnas= ISNULL(@Columnas + ',','') + QUOTENAME(Curso)
FROM (SELECT DISTINCT Curso FROM #Ingresos) AS SCR
-- Cadena PIVOT Dinamico
SET @PivotDinamico =
'SELECT *
FROM #Ingresos
PIVOT(SUM(Ganancias)
FOR Curso IN (' + @Columnas + ')) AS PVT'
-- Ejecutamos la cadena del Pivot Dinamico
EXEC (@PivotDinamico)
Tambin se puede haber generado las columnas del PIVOT del siguiente modo:
SELECT @Columnas= COALESCE(@columnas + ', ', '') + QUOTENAME(Curso)
FROM (SELECT DISTINCT Curso FROM #Ingresos) AS SCR
Ref: [Link]