FROM: uso de PIVOT y UNPIVOT

• 14/10/2019
• 5 minutos de lectura
• • V
  • c
  • M
  • i
  • M
  • +7

Se aplica a: SQL Server (todas las versiones compatibles) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Almacenamiento de datos en paralelo

Puede utilizar los operadores relacionales PIVOT y UNPIVOT para cambiar una expresión con valores de tabla en otra tabla. PIVOT rota una expresión con valores de tabla convirtiendo los valores únicos de una columna en la expresión en varias columnas en la salida. Y PIVOT ejecuta agregaciones donde «se requieren en cualquier valor de columna restante que se desee en el resultado final. UNPIVOT realiza la operación opuesta a PIVOT mediante la rotación de columnas de una expresión con valores de tabla en valores de columna.

La sintaxis de PIVOT proporciona es más simple y más legible que la sintaxis que de otro modo podría ser especificado en una serie compleja de SELECT...CASE instrucciones. Para obtener una descripción completa de la sintaxis de PIVOT, consulte FROM (Transact-SQL).

Sintaxis

La siguiente sintaxis resume cómo utilizar el operador PIVOT.

Comentarios

Los identificadores de columna en la cláusula UNPIVOT siguen la intercalación del catálogo. Para SQL Database, la intercalación es siempre SQL_Latin1_General_CP1_CI_AS. Para SQL Server parcialmente contenido bases de datos, la intercalación es siempre Latin1_General_100_CI_AS_KS_WS_SC. Si la columna es combin ed con otras columnas, se requiere una cláusula de intercalación (COLLATE DATABASE_DEFAULT) para evitar conflictos.

Ejemplo básico de PIVOT

El siguiente ejemplo de código produce una tabla de dos columnas que tiene cuatro filas.

Aquí está el conjunto de resultados.

No hay productos definidos con tres DaysToManufacture.

El siguiente código muestra el mismo resultado, pivotado para que el DaysToManufacture los valores se convierten en los títulos de las columnas. Se proporciona una columna para tres días, aunque los resultados son NULL.

Aquí está el conjunto de resultados .

Ejemplo de PIVOT complejo

Un escenario común donde PIVOT puede ser útil es cuando desee generar informes de tabulación cruzada para dar un resumen de los datos. Por ejemplo, suponga que desea consultar la tabla PurchaseOrderHeader en la base de datos de muestra AdventureWorks2014 para determinar el número de pedidos realizados por determinados empleados. La siguiente consulta proporciona este informe, ordenado por proveedor.

Aquí hay un conjunto de resultados parciales.

Los resultados devueltos por esta subselección La declaración se basa en la columna EmployeeID.

Los valores únicos devueltos por se convierte en campos en el conjunto de resultados final. Como tal, hay «una columna para cada EmployeeID número especificado en la cláusula dinámica: en este caso, los empleados 250, 251, 256, 257 y 260. El PurchaseOrderID columna sirve como columna de valor, contra la cual se agrupan las columnas devueltas en el resultado final, que se denominan columnas de agrupación. En este caso, las columnas de agrupación se agregan por COUNT función. Observe que aparece un mensaje de advertencia que indica que los valores nulos que aparecen en la PurchaseOrderID columna «no se consideraron al calcular la COUNT para cada empleado.

Ejemplo UNPIVOT

UNPIVOT realiza casi la operación inversa de PIVOT, rotando columnas en filas. Suponga que la tabla producida en el ejemplo anterior se almacena en la base de datos como pvt y desea rotar los identificadores de columna Emp1, Emp2, Emp3, Emp4 y Emp5 en valores de fila que corresponden a un proveedor en particular. Como tal, debe identificar dos columnas adicionales.La columna que contendrá los valores de columna que «estás rotando (Emp1, Emp2, …) se llamará Employee, y la columna que contendrá los valores que existen actualmente bajo las columnas que se rotan se llamará Orders. Estas columnas corresponden a pivot_column y value_column , respectivamente, en la definición de Transact-SQL. Aquí está la consulta.

Aquí hay un conjunto de resultados parcial.

Observe que UNPIVOT no es «el reverso exacto de PIVOT. PIVOT lleva a cabo una agregación y fusiona varias filas posibles en una sola fila 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 nulos en la entrada de UNPIVOT desaparecen en la salida. Cuando los valores desaparecen, muestra que puede haber valores nulos originales en la entrada antes de la operación PIVOT.

La Sales.vSalesPersonSalesByFiscalYears en la base de datos de muestra AdventureWorks2012 usa PIVOT para devolver las ventas totales de cada vendedor, para cada año fiscal. Para crear un script de la vista en SQL Server Management Studio , en el Explorador de objetos, ubique la vista en la carpeta Vistas para la base de datos AdventureWorks2012. Haga clic con el botón derecho en el nombre de la vista y luego seleccione Vista de secuencia de comandos como.

Consulte también

-SQL)
CASE (Transact-SQL)