FROM: uso de PIVOT y UNPIVOTFROM - Using PIVOT and UNPIVOT

SE APLICA A: síSQL Server síAzure SQL Database síAzure SQL Data Warehouse síAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Se pueden usar los operadores relacionales PIVOT y UNPIVOT para modificar una expresión con valores de tabla en otra tabla.You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. 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 rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. Y PIVOT ejecuta agregaciones donde se requieren en los valores de columna restantes que se desean en la salida final.And PIVOT runs aggregations where they're required on any remaining column values that are wanted in the final output. UNPIVOT realiza la operación contraria a PIVOT girando las columnas de una expresión con valores de tabla a valores de columna.UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

La sintaxis de PIVOT es más sencilla y legible que la sintaxis que se puede especificar en una serie compleja de instrucciones SELECT...CASE.The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements. Para obtener una descripción completa de la sintaxis de PIVOT, vea FROM (Transact-SQL).For a complete description of the syntax for PIVOT, see FROM (Transact-SQL).

SintaxisSyntax

La sintaxis siguiente resume cómo se usa el operador PIVOT.The following syntax summarizes how to use the PIVOT operator.

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>;  

NotasRemarks

Los identificadores de columna de la cláusula UNPIVOT siguen la intercalación del catálogo.The column identifiers in the UNPIVOT clause follow the catalog collation. Para SQL DatabaseSQL Database, la intercalación es siempre SQL_Latin1_General_CP1_CI_AS.For SQL DatabaseSQL Database, the collation is always SQL_Latin1_General_CP1_CI_AS. Para las bases de datos parcialmente independientes de SQL ServerSQL Server, la intercalación es siempre Latin1_General_100_CI_AS_KS_WS_SC.For SQL ServerSQL Server partially contained databases, the collation is always 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.If the column is combined with other columns, then a collate clause (COLLATE DATABASE_DEFAULT) is required to avoid conflicts.

Ejemplo PIVOT básicoBasic PIVOT Example

En el ejemplo de código siguiente se genera una tabla de dos columnas con cuatro filas.The following code example produces a two-column table that has four rows.

USE AdventureWorks2014 ;  
GO  
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost   
FROM Production.Product  
GROUP BY DaysToManufacture;  

El conjunto de resultados es el siguiente.Here is the result set.

DaysToManufacture AverageCost
----------------- -----------
0                 5.0885
1                 223.88
2                 359.1082
4                 949.4105

No hay productos definidos con tres DaysToManufacture.No products are defined with three DaysToManufacture.

En el código siguiente se muestra el mismo resultado, dinamizado para que los valores de DaysToManufacture se conviertan en encabezados de columna.The following code displays the same result, pivoted so that the DaysToManufacture values become the column headings. Se proporciona una columna para tres [3] días, aunque los resultados son NULL.A column is provided for three [3] days, even though the results are 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;  
  

El conjunto de resultados es el siguiente.Here is the result set.

Cost_Sorted_By_Production_Days 0           1           2           3           4         
------------------------------ ----------- ----------- ----------- ----------- -----------
AverageCost                    5.0885      223.88      359.1082    NULL        949.4105

Ejemplo PIVOT complejoComplex PIVOT Example

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.A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to give a summary of the data. Por ejemplo, suponga que desea consultar la tabla PurchaseOrderHeader en la base de datos de ejemplo AdventureWorks2014 para determinar el número de pedidos de compra colocados por ciertos empleados.For example, suppose you want to query the PurchaseOrderHeader table in the AdventureWorks2014 sample database to determine the number of purchase orders placed by certain employees. En la siguiente consulta se proporciona este informe, ordenado por proveedor.The following query provides this report, ordered by vendor.

USE AdventureWorks2014;  
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.Here is a partial result set.

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.The results returned by this subselect statement are pivoted on the EmployeeID column.

SELECT PurchaseOrderID, EmployeeID, VendorID  
FROM PurchaseOrderHeader;  

Los valores únicos devueltos por la columna EmployeeID se convierten en campos en el conjunto de resultados finales.The unique values returned by the EmployeeID column become fields in the final result set. Como tal, hay una columna para cada número de EmployeeID especificado en la cláusula dinámica: en este caso los empleados 164, 198, 223, 231 y 233.As such, there's a column for each EmployeeID number specified in the pivot clause: in this case employees 164, 198, 223, 231, and 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.The PurchaseOrderID column serves as the value column, against which the columns returned in the final output, which are called the grouping columns, are grouped. En este caso, las columnas de agrupamiento se agregan mediante la función COUNT.In this case, the grouping columns are aggregated by the COUNT function. 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.Notice that a warning message appears that indicates that any null values appearing in the PurchaseOrderID column weren't considered when computing the COUNT for each employee.

Importante

Cuando se usan 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.When aggregate functions are used with PIVOT, the presence of any null values in the value column are not considered when computing an aggregation.

UNPIVOT realiza casi la operación inversa de PIVOT, girando columnas en filas.UNPIVOT carries out almost the reverse operation of PIVOT, by rotating columns into rows. 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.Suppose the table produced in the previous example is stored in the database as pvt, and you want to rotate the column identifiers Emp1, Emp2, Emp3, Emp4, and Emp5 into row values that correspond to a particular vendor. Como tal, debe identificar dos columnas adicionales.As such, you must identify two additional columns. 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 existen actualmente en las columnas que se giran se denominará Orders.The column that will contain the column values that you're rotating (Emp1, Emp2,...) will be called Employee, and the column that will hold the values that currently exist under the columns being rotated will be called Orders. Estas columnas corresponden a columna_dinámica y columna_de_valor, respectivamente, en la definición de Transact-SQLTransact-SQL.These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQLTransact-SQL definition. Esta es la consulta.Here is the query.

-- 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.Here is a partial result set.

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 lo contrario a PIVOT.Notice that UNPIVOT isn't the exact reverse of PIVOT. PIVOT realiza una agregación y combina posibles múltiples filas en una sola fila en la salida.PIVOT carries out an aggregation and merges possible multiple rows into a single row in the output. UNPIVOT no reproduce el resultado de la expresión con valores de tabla original porque las filas se han combinado.UNPIVOT doesn't reproduce the original table-valued expression result because rows have been merged. Además, los valores null de la entrada de UNPIVOT desaparecen en la salida.Also, null values in the input of UNPIVOT disappear in the output. Cuando los valores desaparecen, se muestra que puede haber habido valores null originales en la entrada antes de la operación PIVOT.When the values disappear, it shows that there may have been original null values in the input before the PIVOT operation.

En la vista Sales.vSalesPersonSalesByFiscalYears de la base de datos de ejemplo AdventureWorks2012AdventureWorks2012 se usa PIVOT para devolver el total de ventas de cada vendedor, para cada año fiscal.The Sales.vSalesPersonSalesByFiscalYears view in the AdventureWorks2012AdventureWorks2012 sample database uses PIVOT to return the total sales for each salesperson, for each fiscal year. Para generar el script de la vista en SQL Server Management StudioSQL Server Management Studio, en el Explorador de objetos, localice la vista en la carpeta Views de la base de datos AdventureWorks2012AdventureWorks2012.To script the view in SQL Server Management StudioSQL Server Management Studio, in Object Explorer, locate the view under the Views folder for the AdventureWorks2012AdventureWorks2012 database. Haga clic con el botón derecho en el nombre de la vista y después seleccione Incluir vista como.Right-click the view name, and then select Script View as.

Consulte tambiénSee Also

FROM (Transact-SQL) FROM (Transact-SQL)
CASE (Transact-SQL)CASE (Transact-SQL)