Usar PIVOT y UNPIVOT
Puede usar los operadores relacionales PIVOT y UNPIVOT para modificar una expresión con valores de tabla en otra tabla. PIVOT gira una expresión con valores de tabla convirtiendo los valores únicos de una columna de la expresión en varias columnas en la salida y realiza agregaciones donde son necesarias en cualquier valor de columna restante que se desee en la salida final. UNPIVOT realiza la operación contraria a PIVOT girando las columnas de una expresión con valores de tabla a valores de columna.
[!NOTA]
Cuando se utilizan las palabras clave PIVOT o UNPIVOT en bases de datos actualizadas a SQL Server 2005 o posterior, el nivel de compatibilidad de la base de datos debe estar establecido a un mínimo de 90. Para obtener información sobre el procedimiento sobre el nivel de compatibilidad de la base de datos , vea sp_dbcmptlevel (Transact-SQL).
La sintaxis que proporciona PIVOT es más sencilla y más legible que la sintaxis que se puede especificar en una serie compleja de instrucciones SELECT...CASE. Para obtener una descripción completa de la sintaxis de PIVOT, vea FROM (Transact-SQL).
A continuación, se muestra una sintaxis anotada de PIVOT.
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
Ejemplo PIVOT básico
En el ejemplo de código siguiente se genera una tabla de dos columnas con cuatro filas.
USE AdventureWorks ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
Éste es el conjunto de resultados.
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105
No hay productos definidos con tres DaysToManufacture.
En el código siguiente se muestra el mismo resultado, dinamizado para que los valores de DaysToManufacture se conviertan en encabezados de columna. Se proporciona una columna para tres [3] días, aunque los resultados son NULL.
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
Éste es el conjunto de resultados.
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
Ejemplo PIVOT complejo
Un escenario habitual en el que PIVOT puede ser útil es cuando se desea generar informes de tabla cruzada para resumir datos. Por ejemplo, suponga que desea consultar la tabla PurchaseOrderHeader en la base de datos de ejemplo AdventureWorks para determinar el número de pedidos de compra colocados por ciertos empleados. En la siguiente consulta se proporciona este informe, ordenado por proveedor.
USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY pvt.VendorID;
A continuación se muestra un conjunto de resultados parciales.
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5
Los resultados devueltos por esta instrucción de subselección se dinamizan en la columna EmployeeID.
SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader;
Esto significa que los valores únicos devueltos por la columna EmployeeID se convierten en campos del conjunto de resultados finales. Por lo tanto, hay una columna para cada número de EmployeeID especificado en la cláusula dinámica: en este caso empleados 164, 198, 223, 231 y 233. La columna PurchaseOrderID se utiliza como columna de valores, respecto a la que se ordenan las columnas del resultado final, denominadas columnas de agrupamiento. En este caso, las columnas de agrupamiento se agregan mediante la función COUNT. Tenga presente que aparece un mensaje de advertencia que indica que los valores NULL que aparecen en la columna PurchaseOrderID no se tuvieron en cuenta cuando se contabilizó COUNT para cada empleado.
.gif "Nota importante") Importante |
|---|
Al utilizar funciones de agregado con PIVOT, la presencia de valores NULL en la columna de valores no se tiene en cuenta cuando se calcula una agregación. |
UNPIVOT realiza casi la operación inversa de PIVOT, girando columnas a filas. Suponga que la tabla producida en el ejemplo anterior se almacena en la base de datos como pvt y que desea girar los identificadores de columna Emp1, Emp2, Emp3, Emp4 y Emp5 a valores de fila que correspondan a un determinado proveedor. Esto significa que debe identificar dos columnas adicionales. La columna que contendrá los valores de columna que se están girando (Emp1, Emp2,...) se denominará Employee y la columna que contendrá los valores que residen actualmente en las columnas que se giran se denominará Orders. Estas columnas corresponden a pivot_column y value_column, respectivamente, en la definición de Transact-SQL. Ésta es la consulta.
--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO
A continuación se muestra un conjunto de resultados parciales.
VendorID Employee Orders
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5
...
Tenga en cuenta que UNPIVOT no es exactamente el operador inverso a PIVOT. PIVOT realiza una agregación y, por tanto, combina posibles múltiples filas en una fila única en la salida. UNPIVOT no reproduce el resultado de la expresión con valores de tabla original porque las filas se han combinado. Además, los valores NULL de la entrada de UNPIVOT desaparecen en la salida, mientras que pueden haber sido valores NULL en la entrada antes de la operación PIVOT.
La vista Sales.vSalesPersonSalesByFiscalYears de la base de datos de ejemplo AdventureWorks utiliza PIVOT para devolver el total de ventas de cada vendedor, para cada año fiscal. Para generar el script de la vista en SQL Server Management Studio, en el Explorador de objetos, localícela en la carpeta Views de la base de datos AdventureWorks. Haga clic con el botón secundario en el nombre de la vista y seleccione Incluir vista como.