Servidor SQL: cláusula PIVOT

Los datos expuestos en este blog, son solo de índole informativo. Por favor realiza siempre una copia de seguridad antes de realizar cualquier cambio en tu proyecto.

Servidor SQL: cláusula PIVOT

Este tutorial de SQL Server describe cómo usar cláusula PIVOTE Sintaxis y ejemplos en SQL Server (Transact-SQL).

describir

La cláusula PIVOT de SQL Server (Transact-SQL) le permite escribir tablas de referencias cruzadas. Esto significa que puede agregar los resultados y rotar las filas en columnas.

sintaxis

La sintaxis de la cláusula PIVOT en SQL Server (Transact-SQL) es:

SELECT first_column AS <first_column_alias>,
[pivot_value1], [pivot_value2], ... [pivot_value_n]
FROM 
(<source_table>) AS <source_table_alias>
PIVOT 
(
 aggregate_function(<aggregate_column>)
 FOR <pivot_column>
 IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])
) AS <pivot_table_alias>;

parámetro o parámetro

first_column es la columna o expresión que aparecerá como la primera columna en la tabla dinámica. first_column_alias Encabezado de columna para la primera columna de la tabla dinámica. pivot_value1, pivot_value2, … pivot_value_n Lista de valores para rotar. source_table La declaración SELECT que proporciona los datos de origen para la tabla dinámica. source_table_alias Alias ​​para source_table. added_function Función agregada, como SUM, COUNT, MIN, MAX o AVG. added_column Columna o expresión que se usará con added_function. pivot_column Columna que contiene valores de pivote. pivot_table_alias Alias ​​de tabla dinámica.

aplicar para

La cláusula PIVOT está disponible en las siguientes versiones de SQL Server (Transact-SQL):

• Servidor SQL 2017, Servidor SQL 2016, Servidor SQL 2014, Servidor SQL 2012, Servidor SQL 2008 R2, Servidor SQL 2008, Servidor SQL 2005

Ejemplo de DDL/DML

Si desea seguir este tutorial, obtenga DDL para crear las tablas y DML para completar los datos. ¡Entonces pruebe los ejemplos en su propia base de datos!

ejemplo

La cláusula PIVOT está disponible para SQL Server (Transact-SQL).

Veamos un ejemplo. Si tenemos una tabla de empleados con los siguientes datos:

ID de empleado	apellido	nombre	salario	dept_id
12009	Sutherland	Bárbara	54000	45
34974	Yates	Fred	80000	45
34987	Erickson	neil	42000	45
45001	parker	Salida	57500	30
75623	puertas	Steve	65000	30

Ejecutamos la siguiente instrucción SQL, que utiliza la cláusula PIVOT para crear una consulta de tabla cruzada:

SELECT 'TotalSalary' AS TotalSalaryByDept, 
[30], [45]
FROM
(SELECT dept_id, salary
 FROM employees) AS SourceTable
PIVOT
(
 SUM(salary)
 FOR dept_id IN ([30], [45])
) AS PivotTable;

Devolverá los siguientes resultados:

Salario total del departamento	30	45
salario bruto	122500	176000

Este ejemplo crea una tabla dinámica que muestra el salario total para dept_id 30 y dept_id 45. Los resultados se muestran en una fila y los dos departamentos aparecen en sus respectivas columnas.

Ahora, analicemos la cláusula PIVOT y expliquemos cómo funciona.

Especificar columnas en los resultados de la tabla de referencias cruzadas

Primero, queremos especificar qué campos se incluyen en los resultados de la tabla cruzada. En este ejemplo, queremos el valor literal “TotalSalary” como la primera columna de la tabla dinámica. Queremos crear una columna para dept_id 30 y una segunda columna para dept_id 45. Esto le da a nuestra tabla dinámica 3 columnas.

SELECT 'TotalSalary' AS TotalSalaryByDept, 
[30], [45]

Especificar los datos de la tabla de origen

A continuación, debemos especificar una declaración SELECT que devolverá los datos de origen de la tabla dinámica.

En este ejemplo, queremos devolver los valores dept_id y de salario de la tabla de empleados:

(SELECT dept_id, salary
 FROM employees) AS SourceTable

Debe especificar un alias para la consulta de origen. En este ejemplo, asignamos un alias a la consulta a SourceTable.

especificar función agregada

A continuación, debemos especificar la función agregada que se usará al crear la consulta de tabla cruzada. Puede utilizar cualquier agregado, como las funciones SUM, COUNT, MIN, MAX o AVG.

En este ejemplo, usaremos la función SUMA. Esto agregará valores de salario:

PIVOT
(SUM(salary)

Especificar el valor de pivote

Finalmente, necesitamos especificar el valor de pivote para incluir en el resultado. Estos se utilizarán como encabezados de columna en nuestra consulta de tabla cruzada.

En este ejemplo, solo devolveremos los valores dept_id 30 y 45. Estos valores serán los encabezados de columna en nuestra tabla dinámica. Además, tenga en cuenta que estos valores son una lista finita de valores dept_id, no necesariamente todos los valores posibles.

FOR dept_id IN ([30], [45])

Ahora, cuando los juntamos, obtenemos la siguiente tabla dinámica:

Salario total del departamento	30	45
salario bruto	122500	176000