Escritura de consultas que dinamicen y anulen la dinamización de conjuntos de resultados
Use pivot en SQL Server para girar la forma en que se muestran los datos de una orientación basada en filas a una orientación basada en columnas. Al realizar un pivot, consolida los valores de una columna en una lista de valores distintos y, a continuación, la proyecta como encabezados de columna. Normalmente, esto incluye la agregación de valores en las nuevas columnas.
Por ejemplo, los datos de origen parciales siguientes enumeran los valores repetidos para Category y Orderyear, junto con los valores de Qty, para cada instancia de un par Category/Orderyear :
| Categoría | Cantidad | Año de Pedido |
|---|---|---|
| Productos lácteos | 12 | 2019 |
| Granos/cereales | 10 | 2019 |
| Productos lácteos | 5 | 2019 |
| Marisco | 2 | 2020 |
| Repostería | 36 | 2020 |
| Condimentos | 35 | 2020 |
| Repostería | 55 | 2020 |
| Condimentos | 16 | 2020 |
| Productos lácteos | 60 | 2020 |
| Productos lácteos | 20 | 2020 |
| Repostería | 24 | 2020 |
| ... (2155 filas afectadas) |
La tabla anterior representa más de 2000 filas, con muchos valores duplicados. Para analizar los resultados por categoría y año, es posible que desee organizar los valores que se muestran de la manera siguiente, sumando la columna Qty a lo largo del camino:
| Categoría | 2019 | 2020 | 2021 |
|---|---|---|---|
| Bebidas | 1842 | 3996 | 3694 |
| Condimentos | 962 | 2895 | 1441 |
| Repostería | 1357 | 4137 | 2412 |
| Productos lácteos | 2086 | 4374 | 2689 |
| Granos/cereales | 549 | 2636 | 1377 |
| Carne o aves de corral | 950 | 2189 | 1060 |
| Productos | 549 | 1583 | 858 |
| Marisco | 1286 | 3679 | 2716 |
| (8 filas afectadas) |
El conjunto de resultados ahora tiene ocho filas. En el proceso de pivotaje, cada año distinto se creó como título de columna, y los valores de la columna Qty se agruparon por Categoría y se agregaron.
Uso de PIVOT para dinamizar un conjunto de resultados
Puede pivotar un conjunto de resultados mediante el operador PIVOT. El operador de tabla Transact-SQL PIVOT funciona en la salida de la cláusula FROM en una instrucción SELECT. Para usar PIVOT, debe proporcionar tres elementos al operador :
- Agrupación: en la cláusula FROM, se proporcionan las columnas de entrada. A partir de esas columnas, PIVOT determinará qué columnas se usarán para agrupar los datos para la agregación. Esto se basa en ver qué columnas no se usan como otros elementos del operador PIVOT.
- Propagación: se proporciona una lista delimitada por comas de valores que se usarán como encabezados de columna para los datos dinamizado. Los valores deben estar presentes en los datos de origen.
- Agregación: se proporciona una función de agregación (SUM, etc.) que se va a realizar en las filas agrupadas.
Además, debe asignar un alias de tabla a la tabla de resultados del operador PIVOT. En el ejemplo siguiente se muestran los elementos en su lugar:
SELECT Category, [2019],[2020],[2021]
FROM ( SELECT Category, Qty, Orderyear FROM Sales.CategoryQtyYear) AS D
PIVOT(SUM(qty) FOR orderyear IN ([2019],[2020],[2021])) AS pvt;
En el ejemplo anterior, Orderyear es la columna que proporciona los valores de propagación, Qty se usa para la agregación y Category para la agrupación. Los valores orderyear se incluyen en delimitadores para indicar que son identificadores de columnas en el resultado.
Uso de UNPIVOT para anular la dinamización de un conjunto de resultados
Anular la dinamización de datos es el proceso lógico inverso de dinamizar datos. En lugar de convertir filas en columnas, la anulación de dinamización convierte las columnas en filas. Se trata de una técnica útil para tomar datos que ya se dinamizaron (con o sin usar un operador PIVOT de Transact-SQL) y devolverlo a una presentación tabular orientada a filas. Puede usar el operador de tabla UNPIVOT para lograrlo.
Para usar el operador UNPIVOT, proporcione tres elementos:
- Columnas de origen cuya dinamización se anulará.
- Nombre de la nueva columna que mostrará los valores no aprobados.
- Nombre de la columna que mostrará los nombres de los valores no aprobados.
En el ejemplo siguiente se especifica 2019, 2020 y 2021 como columnas que se van a despivotar, utilizando el nuevo nombre de columna orderyear y los valores qty que se mostrarán en una nueva columna qty .
SELECT category, qty, orderyear
FROM Sales.PivotedCategorySales
UNPIVOT(qty FOR orderyear IN([2019],[2020],[2021])) AS unpvt;
Al anular la dinamización de los datos, una o varias columnas se definen como el origen que se va a convertir en filas. Los datos de esas columnas se reparten, o dividen, en una o varias filas nuevas, en función de en cuántas columnas se está anulando la dinamización. En los siguientes datos de origen, hay tres columnas cuya dinamización no se anulará. Cada valor Orderyear se copiará en una nueva fila y se asociará con su valor Category . Los NULOS se eliminarán durante el proceso y no se creará ninguna fila.
| Categoría | 2019 | 2020 | 2021 |
|---|---|---|---|
| Bebidas | 1842 | 3996 | 3694 |
| Condimentos | 962 | 2895 | 1441 |
| Repostería | 1357 | 4137 | 2412 |
| Productos lácteos | 2086 | 374 | 2689 |
| Granos/cereales | 549 | 2636 | 1377 |
| Carne o aves de corral | 950 | 2189 | 1060 |
| Productos | 549 | 1583 | 858 |
| Marisco | 1286 | 3679 | 2716 |
Para cada intersección de Category y Orderyear, se creará una nueva fila, como en estos resultados parciales:
| categoría | Cantidad | orderyear |
|---|---|---|
| Bebidas | 1842 | 2019 |
| Bebidas | 3996 | 2020 |
| Bebidas | 3694 | 2021 |
| Condimentos | 962 | 2019 |
| Condimentos | 2895 | 2020 |
| Condimentos | 1441 | 2021 |
| Repostería | 1357 | 2019 |
| Repostería | 4137 | 2020 |
| Repostería | 2412 | 2021 |
Al anular la dinamización no se restauran los datos originales. Los datos de nivel de detalle se perdieron durante el proceso de agregación en la dinamización original. UNPIVOT no tiene capacidad para asignar valores para volver a los valores de detalle originales.