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 dinamizar, los valores de una columna se consolidan en una lista de valores distintos y, a continuación, se proyecta esa lista como encabezados de columna. Normalmente, esto incluye la agregación a los valores de columna de 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:
| Category | Cantidad | Orderyear |
|---|---|---|
| Productos lácteos | 12 | 2019 |
| Grains/Cereals | 10 | 2019 |
| Productos lácteos | 5 | 2019 |
| Marisco | 2 | 2020 |
| Repostería | 36 | 2020 |
| Condiments | 35 | 2020 |
| Repostería | 55 | 2020 |
| Condiments | 16 | 2020 |
| Productos lácteos | 60 | 2020 |
| Productos lácteos | 20 | 2020 |
| Repostería | 24 | 2020 |
| (2155 fila(s) afectada(s)) |
La tabla anterior representa más de 2000 filas, con muchos valores duplicados. Para analizar los resultados por categoría y año, puede organizar los valores que se mostrarán de la siguiente manera, sumando la columna Qty a lo largo del proceso:
| Category | 2019 | 2020 | 2021 |
|---|---|---|---|
| Beverages | 1842 | 3996 | 3694 |
| Condiments | 962 | 2895 | 1441 |
| Repostería | 1357 | 4137 | 2412 |
| Productos lácteos | 2086 | 4374 | 2689 |
| Grains/Cereals | 549 | 2636 | 1377 |
| Meat/Poultry | 950 | 2189 | 1060 |
| Productos | 549 | 1583 | 858 |
| Marisco | 1286 | 3679 | 2716 |
| (8 fila(s) afectada(s)) |
El conjunto de resultados ahora es un total de ocho filas. En el proceso de dinamización, cada año distinto se creó como un encabezado 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 dinamizar 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 la búsqueda de las columnas que 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 aparecer en los datos de origen.
- Agregación: proporciona una función de agregación (SUM, y así sucesivamente) 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 entre 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. Para ello, puede usar el operador de tabla UNPIVOT.
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 especifican 2019, 2020 y 2021 como columnas cuya dinamización se anulará, con 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á a su valor Category. Los valores NULL se quitarán en el proceso y no se creará ninguna fila:
| Category | 2019 | 2020 | 2021 |
|---|---|---|---|
| Beverages | 1842 | 3996 | 3694 |
| Condiments | 962 | 2895 | 1441 |
| Repostería | 1357 | 4137 | 2412 |
| Productos lácteos | 2086 | 374 | 2689 |
| Grains/Cereals | 549 | 2636 | 1377 |
| Meat/Poultry | 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:
| category | Cantidad | orderyear |
|---|---|---|
| Beverages | 1842 | 2019 |
| Beverages | 3996 | 2020 |
| Beverages | 3694 | 2021 |
| Condiments | 962 | 2019 |
| Condiments | 2895 | 2020 |
| Condiments | 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.