FROM: uso de PIVOT y UNPIVOT
Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Punto de conexión de análisis SQL en Microsoft Fabric Warehouse en Microsoft Fabric Base de datos SQL de Microsoft Fabric
Se pueden 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 al convertir los valores únicos de una columna en la expresión en varias columnas en la salida. PIVOT
también ejecuta agregaciones en las que se requieren en los valores de columna restantes que se desean en la salida final. UNPIVOT
lleva a cabo la operación opuesta a PIVOT
, girando las columnas de una expresión con valores de tabla en valores de columna.
La sintaxis de PIVOT
es más fácil y legible que la sintaxis que podría especificarse en una serie compleja de SELECT...CASE
instrucciones. Para obtener una descripción completa de la sintaxis de PIVOT
, vea cláusula FROM.
Nota:
El uso repetido de dentro de una sola instrucción T-SQL puede afectar negativamente al rendimiento de PIVOT
/UNPIVOT
las consultas.
Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022
o AdventureWorksDW2022
, que se puede descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.
Sintaxis
En esta sección se resume cómo usar el PIVOT
operador y UNPIVOT
.
Sintaxis del PIVOT
operador .
SELECT [ <non-pivoted column> [ AS <column name> ] , ]
...
[ <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 become column headers>
IN ( <first pivoted column>
, <second pivoted column>
, ... <last pivoted column> )
) AS <alias for the pivot table>
[ <optional ORDER BY clause> ]
[ ; ]
Sintaxis del UNPIVOT
operador .
SELECT [ <non-pivoted column> [ AS <column name> ] , ]
...
[ <output column for names of the pivot columns> [ AS <column name> ] , ]
[ <new output column created for values in result of the source query> [ AS <column name> ] ]
FROM
( <SELECT query that produces the data> )
AS <alias for the source query>
UNPIVOT
(
<new output column created for values in result of the source query>
FOR <output column for names of the pivot columns>
IN ( <first pivoted column>
, <second pivoted column>
, ... <last pivoted column> )
)
[ <optional ORDER BY clause> ]
[ ; ]
Comentarios
Los identificadores de columna de la cláusula UNPIVOT
siguen la intercalación del catálogo.
Para Azure SQL Database, la intercalación siempre
SQL_Latin1_General_CP1_CI_AS
es .Para las bases de datos parcialmente independientes de SQL Server, la intercalación es siempre
Latin1_General_100_CI_AS_KS_WS_SC
.
Si la columna se combina con otras columnas, se necesita una cláusula COLLATE (COLLATE DATABASE_DEFAULT
) para evitar conflictos.
En los grupos de Microsoft Fabric y Azure Synapse Analytics, las consultas con PIVOT
operador producen un error si hay un GROUP BY
elemento en la salida de columna no dinámica por PIVOT
. Como solución alternativa, quite la columna no dinámica de GROUP BY
. Los resultados de la consulta son los mismos, ya que esta GROUP BY
cláusula es un duplicado.
Ejemplo PIVOT básico
En el ejemplo de código siguiente se genera una tabla de dos columnas con cuatro filas.
USE AdventureWorks2022;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
Este es el conjunto de resultados.
DaysToManufacture AverageCost
------------------ ------------
0 5.0885
1 223.88
2 359.1082
4 949.4105
No se define ningún producto con un valor de 3
para 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 durante tres ([3]
) días, aunque los resultados sean NULL
.
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS CostSortedByProductionDays,
[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;
Este es el conjunto de resultados.
CostSortedByProductionDays 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 tabulación cruzada para proporcionar un resumen de los datos. Por ejemplo, suponga que desea consultar la tabla PurchaseOrderHeader
en la base de datos de ejemplo AdventureWorks2022
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 AdventureWorks2022;
GO
SELECT VendorID,
[250] AS Emp1,
[251] AS Emp2,
[256] AS Emp3,
[257] AS Emp4,
[260] AS Emp5
FROM
(
SELECT PurchaseOrderID,
EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader
) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN ([250], [251], [256], [257], [260])
) AS pvt
ORDER BY pvt.VendorID;
A continuación se muestra un conjunto parcial de resultados.
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
----------- ----------- ----------- ----------- ----------- -----------
1492 2 5 4 4 4
1494 2 5 4 5 4
1496 2 4 4 5 5
1498 2 5 4 4 4
1500 3 4 4 5 4
Los resultados devueltos por esta instrucción de subselección se dinamizan en la columna EmployeeID
.
SELECT PurchaseOrderID,
EmployeeID,
VendorID
FROM PurchaseOrderHeader;
Los valores únicos devueltos por la columna EmployeeID
se convierten en campos en el conjunto de resultados finales. Por lo tanto, hay una columna para cada EmployeeID
número especificado en la cláusula dinámica, que son empleados 250
, 251
, 256
, , 257
y 260
en este ejemplo. 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
. Aparece un mensaje de advertencia que indica que los valores NULL que aparecen en la PurchaseOrderID
columna no se consideraron al calcular el para COUNT
cada empleado.
Importante
Cuando se usan funciones de agregado con PIVOT
, no se tiene en cuenta la presencia de valores NULL en la columna value al calcular una agregación.
Ejemplo UNPIVOT
UNPIVOT
realiza casi la operación inversa de PIVOT
, girando columnas en 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. Por lo tanto, debe identificar dos columnas adicionales.
La columna que contiene los valores de columna que está girando (Emp1
, Emp2
, etc.) se denomina Employee
y la columna que contiene los valores que existen actualmente en las columnas que se giran, se denomina Orders
. Estas columnas corresponden a pivot_column y value_column, respectivamente, en la definición de Transact-SQL. Aquí está 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 parcial de resultados.
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
UNPIVOT
no es la inversa exacta de PIVOT
. PIVOT
realiza una agregación y combina posibles múltiples filas en una sola fila en la salida. UNPIVOT
no reproduce el resultado de la expresión con valores de tabla original, ya que se han combinado filas. Además, NULL
los valores de la entrada de UNPIVOT
desaparecen en la salida. Cuando los valores desaparecen, muestra que puede haber valores originales NULL
en la entrada antes de la PIVOT
operación.
En la vista Sales.vSalesPersonSalesByFiscalYears
de la base de datos de ejemplo AdventureWorks2022
se usa 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, localice la vista en la carpeta Views de la base de datos AdventureWorks2022
. Haga clic con el botón derecho en el nombre de la vista y después seleccione Incluir vista como.