Resumen: en este tutorial, aprenderá a usar el operador SQL Server PIVOT para convertir filas en columnas.
Configuración de los objetivos
Para la demostración, usaremos production.products y production.categories tablas de la base de datos de muestra:
La siguiente consulta encuentra la cantidad de productos para cada categoría de producto:
Aquí está el resultado:
Nuestro objetivo es convertir los nombres de categoría de la primera columna del resultado en varias columnas y contar la cantidad de productos para cada nombre de categoría como la siguiente imagen:
Además, podemos agregar el año del modelo para agrupar la categ ory por año del modelo como se muestra en el siguiente resultado:
Introducción al operador PIVOT de SQL Server
El operador PIVOT de SQL Server rota una expresión con valores de tabla. Convierte los valores únicos en una columna en varias columnas en la salida y realiza agregaciones en cualquier valor de columna restante.
Siga estos pasos para convertir una consulta en una tabla dinámica:
- Primero, seleccione un conjunto de datos base para pivotar.
- En segundo lugar, cree un resultado temporal usando una tabla derivada o una expresión de tabla común (CTE)
- Tercero, aplique
PIVOToperador.
Apliquemos estos pasos en el siguiente ejemplo.
Primero, seleccione el nombre de la categoría y la identificación del producto de la production.products y production.categories como datos base para la pivotación:
En segundo lugar, cree un conjunto de resultados temporal utilizando una tabla derivada:
En tercer lugar, aplique el operador PIVOT:
Esta consulta genera el siguiente resultado:
Ahora, cualquier columna adicional que agregue a la lista de selección de la consulta que devuelve el base los datos formarán automáticamente grupos de filas en la tabla dinámica. Por ejemplo, puede agregar la columna del año del modelo a la consulta anterior:
Aquí está el resultado:
Generando valores de columna
En la consulta anterior, tenía que escribir el nombre de cada categoría entre paréntesis después de IN operador manualmente. Para evitar esto, puede usar la función QUOTENAME() para generar la lista de nombres de categorías y copiarlos sobre la consulta.
Primero, genere la lista de nombres de categorías:
La salida se verá así:
En este fragmento:
- La función
QUOTENAME()envuelve el el nombre de la categoría entre corchetes, por ejemplo, - La función
LEFT()elimina la última coma de la cadena @columns .
En segundo lugar, copie la lista de nombres de categoría de la salida y péguela en la consulta.
Tablas dinámicas
Si agrega una nuevo nombre de categoría en la tabla production.categories, debe reescribir su consulta, lo cual no es ideal. Para evitar hacer esto, puede usar SQL dinámico para hacer que la tabla dinámica sea dinámica.
En esta consulta, en lugar de pasar una lista fija de nombres de categorías al PIVOT, construimos la lista de nombres de categorías y la pasamos a una declaración SQL, y luego ejecutamos esta declaración dinámicamente usando el procedimiento almacenado sp_executesql.
En este tutorial , ha aprendido a utilizar la tabla PIVOT de SQL Server para convertir filas en columnas.