Tablas dinámicas con columnas desconocidas

Todo pivot estático, ya sea mediante una agregación con CASE, PIVOT de SQL Server o crosstab de Postgres, comparte una limitación: debe enumerar las columnas de salida al escribir la consulta.

Pero ¿qué ocurre si las categorías son desconocidas, como nombres de productos que cambian cada semana o una columna por cada mes activo? Eso es un pivot dinámico, y es una pregunta de entrevista de nivel sénior porque el SQL simple no puede devolver un resultado cuya lista de columnas se decide durante la ejecución.

SQL tiene tipado estático en el nivel del conjunto de resultados: el planificador debe conocer las columnas y sus tipos antes de la ejecución. Una sola consulta no puede decir cree una columna por cada valor que encuentre.

Por eso, la técnica universal consiste en generar el texto SQL en dos pasos: primero consultar las categorías distintas y después construir a partir de ellas una cadena con la consulta de pivot y ejecutar esa cadena.

El primer paso es una consulta normal que enumera los valores distintos que se convertirán en columnas. Normalmente se ordenan para obtener una disposición estable de las columnas.

Este resultado alimenta el paso de construcción de la cadena. En un sistema real, se ejecuta esta consulta, se capturan las filas y se ensambla la siguiente consulta a partir de ellas.

A continuación, convierta esos valores en una lista separada por comas de expresiones CASE (o de nombres entre corchetes para PIVOT). Las bases de datos proporcionan funciones de agregación de cadenas para hacerlo directamente en SQL.

En Postgres es string_agg; en MySQL, GROUP_CONCAT; en SQL Server, STRING_AGG o el antiguo truco de FOR XML PATH.

Concatene el fragmento generado en una cadena de consulta completa y ejecútela mediante ejecución dinámica: EXECUTE en PL/pgSQL, sp_executesql en SQL Server o PREPARE/EXECUTE en MySQL.

Este es el núcleo de un pivot dinámico: SQL escribe SQL y después lo ejecuta.

En Postgres, puede envolver los tres pasos en un bloque DO o en una función. Construya la lista de columnas con string_agg, insértela en la consulta y ejecútela con EXECUTE.

Como las columnas del resultado se desconocen hasta el momento de la ejecución, una función que devuelve este resultado suele utilizar RETURNS SETOF record o devolver las filas como json, que después expande quien la llama.

MySQL no tiene un operador de pivot, por lo que los pivotes dinámicos construyen una cadena de agregación condicional con GROUP_CONCAT y después la ejecutan mediante una sentencia preparada.

GROUP_CONCAT tiene un límite de longitud (group_concat_max_len) que los entrevistadores pueden mencionar; auméntelo si tiene muchas categorías.

Como concatena valores de datos en SQL ejecutable, los pivotes dinámicos conllevan un riesgo de inyección. Si un valor de categoría contiene una comilla o texto malicioso, puede romper o tomar el control de la consulta generada.

Escápese siempre los identificadores y literales con las funciones seguras del motor: format('%I', ...) y %L en Postgres, QUOTENAME en SQL Server. Nunca inserte valores sin procesar directamente en la cadena.

Hay una segunda dificultad importante: quien llama no puede conocer de antemano la estructura del resultado. Estrategias habituales que los entrevistadores aceptan:

• Devolver las filas como JSON y dejar que la capa de aplicación expanda las claves.
• Hacer que el procedimiento muestre o construya la consulta y ejecutarla como un segundo paso.
• Realizar el pivot final en el código de la aplicación (pandas, una herramienta de BI) una vez conocidas las categorías.

No existe una forma limpia de devolver columnas arbitrarias desde una única llamada estática.

Suponga que los productos aparecen y desaparecen, y que el informe necesita una columna de ingresos por cada producto que esté actualmente en sales. No puede codificar la lista de forma fija, así que debe generarla. Postgres lo hace legible: construya el fragmento de CASE con string_agg y un entrecomillado seguro, insértelo en una consulta y después use EXECUTE.

Explíqueselo paso a paso al entrevistador: descubra los productos, convierta cada uno en una columna entrecomillada, ensamble la consulta y ejecútela. La misma estructura se aplica a cualquier motor; solo cambian las funciones auxiliares.

Los candidatos sólidos saben cuándo no hacer esto en SQL. El SQL dinámico es más difícil de leer, probar, proteger y almacenar en caché. A menudo, la mejor respuesta es:

• Devolver el formato largo desde SQL y pivotarlo en la aplicación o en la capa de informes.
• Si el conjunto de categorías es pequeño y cambia lentamente, utilizar un pivot estático y actualizarlo ocasionalmente.

Reserve los pivotes dinámicos para conjuntos de categorías realmente abiertos y en constante cambio.

Ponga a prueba el motivo fundamental por el que existen los pivotes dinámicos.

Los pivotes dinámicos gestionan conjuntos de columnas desconocidos:

• Los pivotes estáticos fallan porque las columnas del resultado deben fijarse antes de la ejecución.
• Patrón: consultar las categorías distintas, construir una cadena SQL de pivot y ejecutarla dinámicamente.
• Utilice string_agg/GROUP_CONCAT/STRING_AGG para construir la lista de columnas.
• Escápese los valores (%I/%L, QUOTENAME) para evitar la inyección SQL.
• A menudo es más limpio devolver el formato largo y hacer el pivot en la capa de aplicación.