Historia de la pregunta.
Este requisito se origina en los pipelines de ingestión de datos que migran de sistemas de hojas de cálculo legacy o exportaciones de archivos planos, donde métricas de series temporales o atributos categóricos se desnormalizan en encabezados de columnas de formato amplio (por ejemplo, Jan_Sales, Feb_Sales) en lugar de filas normalizadas. Tales esquemas son prevalentes en procesos empresariales impulsados por Excel antes de ETL en almacenes relacionales, exigiendo una transformación en tablas de hechos estrechas para permitir análisis temporales y JOINs dimensionales. El desafío radica en transponer esas proyecciones de columna estáticas en flujos de tuplas dinámicos sin recurrir a un procesamiento imperativo fila por fila.
El problema.
Las declaraciones SELECT estándar fijan las identidades de las columnas proyectadas en el tiempo de análisis, impidiendo que una única proyección emita diferentes columnas de origen en diferentes filas de salida sin correlación LATERAL o iteración procedural. El objetivo es fabricar un producto cartesiano entre cada fila de origen y una tabla de dimensión virtual que enumere los nombres de los atributos, luego multiplexar el valor de origen correcto en una columna de resultado genérica a través de lógica condicional. Esto debe lograrse utilizando solo la sintaxis de unión estándar y expresiones escalares disponibles en ANSI SQL:1999 y posteriores.
La solución.
Utilice un CROSS JOIN contra una tabla derivada expresada a través del constructor de filas VALUES, que enumera las claves categóricas (por ejemplo, nombres de meses) como filas. Dentro de la lista SELECT, emplee una expresión CASE buscada que mapea cada clave a su columna de origen correspondiente, proyectando efectivamente el valor desnormalizado en una estructura de fila normalizada. Filtrar el resultado para excluir valores NULL generados cuando un atributo de origen falta para una clave particular, asegurando que la salida final contenga solo mediciones válidas.
SELECT s.cost_center_id, m.fiscal_month, CASE m.fiscal_month WHEN 'M01' THEN s.m01_amt WHEN 'M02' THEN s.m02_amt WHEN 'M03' THEN s.m03_amt -- ... meses adicionales WHEN 'M12' THEN s.m12_amt END AS amount FROM budget_wide s CROSS JOIN ( VALUES ('M01'), ('M02'), ('M03'), ('M04'), ('M05'), ('M06'), ('M07'), ('M08'), ('M09'), ('M10'), ('M11'), ('M12') ) AS m(fiscal_month) WHERE CASE m.fiscal_month WHEN 'M01' THEN s.m01_amt -- ... repita para todos para evitar NULLs END IS NOT NULL;
Un departamento de finanzas exportó asignaciones presupuestarias del año fiscal de un modelo corporativo de Excel a una tabla de preparación, donde doce columnas mensuales (M01_Amt a M12_Amt) representaban períodos de tiempo desnormalizados para cada centro de costos. El almacén de datos destino SAP requería un esquema de tabla de hechos estrecha de (CostCenter_ID, Fiscal_Month, Amount), lo que necesitaba una transformación de deshacer dentro del script de carga de ANSI SQL para evitar procesamiento intermedio en Python. El volumen de cincuenta millones de registros impedía la transformación manual o estrategias de carga de múltiples pasadas.
Solución 1: Union All por columna.
El enfoque inicial utilizó doce consultas SELECT separadas, cada una proyectando una columna de mes diferente codificada en frío en las columnas genéricas Amount y Month_Name, combinadas mediante UNION ALL. Pros: Este método disfruta de compatibilidad universal, funcionando en bases de datos legacy de mainframe y motores SQL arcaicos que carecen de sintaxis de unión moderna. Contras: Realiza doce escaneos completos de la tabla en los datos fuente, resultando en degradación lineal de E/S; el plan de consulta se vuelve voluminoso y difícil de almacenar en caché, y cualquier modificación de esquema (agregar un décimo tercer período) requiere alterar doce listas de proyección separadas.
Solución 2: Generación dinámica de SQL.
Una alternativa implicó construir el texto de consulta dinámicamente en una capa de aplicación iterando sobre tablas de metadatos para generar las ramas necesarias de CASE o brazos de UNION en tiempo de ejecución. Pros: Esto proporciona flexibilidad contra esquemas en evolución y reduce el trabajo manual de autoría de SQL al manejar cientos de columnas. Contras: Infringe la prohibición de lógica procedimental; introduce vectores de ataque de inyección de SQL y sobrecarga de compilación, y la declaración resultante no puede ser encapsulada dentro de una vista de base de datos estática o definición de procedimiento almacenado.
Solución 3: Cross Join con Valores.
La implementación aceptada empleó un CROSS JOIN con un constructor de VALUES que define los doce períodos fiscales, multiplexando el monto correcto a través de una expresión CASE clave en el identificador de período virtual. Pros: Se ejecuta como un solo paso sobre la tabla fuente, aprovecha algoritmos de unión eficientes, y es completamente declarativa y portátil a través de Oracle, SQL Server, PostgreSQL y Db2 sin sugerencias de proveedor. Contras: Requiere soporte de SQL:1999 para constructores de filas, no disponible en sistemas anticuados, y la verbosidad de la expresión CASE aumenta la sobrecarga de mantenimiento a menos que se genere a través de plantillas.
Resultado.
La latencia de transformación disminuyó de veinticinco minutos a menos de noventa segundos al eliminar los escaneos de tabla redundantes inherentes al patrón UNION ALL. El proceso de carga se volvió resiliente a extensiones de esquema, requiriendo solo la adición de una fila al constructor de VALUES cuando se introducen nuevos períodos fiscales. Además, la lógica se encapsuló en una vista estándar, permitiendo consultas ad-hoc directas por parte de usuarios de Tableau sin pasos intermedios de ETL.
¿Cómo evitar que los valores NULL en las columnas de origen aparezcan como filas en el resultado deshecho sin causar que la expresión CASE se evalúe dos veces en el plan de ejecución?
Los candidatos frecuentemente incrustan la expresión CASE dentro de un predicado de cláusula WHERE como WHERE CASE ... END IS NOT NULL, lo que obliga al optimizador a calcular la proyección dos veces—una vez para el filtrado y una vez para la salida. El patrón eficiente de ANSI SQL materializa el resultado dentro de una tabla derivada o Expresión de Tabla Común (CTE): SELECT * FROM (SELECT ..., CASE ... END AS val FROM ... CROSS JOIN ...) sub WHERE val IS NOT NULL. Esto calcula el CASE una vez, filtra las filas y mantiene una separación limpia de preocupaciones para el optimizador de consultas.
Cuando se deshacen columnas con tipos de datos heterogéneos (por ejemplo, una columna de comentarios VARCHAR junto a una columna de monto DECIMAL), ¿qué estrategia de casting específica de ANSI SQL asegura la coherencia de tipos en la única columna de valor resultado sin pérdida de datos?
Muchos candidatos confían incorrectamente en la conversión implícita de tipos, que puede truncar cadenas o perder precisión decimal, o intentan UNION ALL sin darse cuenta de que las reglas de coerción de tipos varían según la plataforma. La solución robusta convierte explícitamente cada columna de origen a un supertipo común—típicamente VARCHAR—dentro de cada rama WHEN de la expresión CASE: CASE WHEN key='Comment' THEN CAST(text_col AS VARCHAR(500)) ELSE CAST(num_col AS VARCHAR(500)) END. Esto garantiza que todos los valores devueltos compartan un único tipo de dato compatible con la definición de la columna de resultado, preservando la representación textual de datos numéricos donde sea necesario.
¿Por qué el enfoque de CROSS JOIN con VALUES parece superficialmente crear una explosión de producto cartesiano, y cómo mitiga típicamente el optimizador esto en comparación con el comportamiento de eliminación NULL de un operador UNPIVOT nativo?
El CROSS JOIN genera lógicamente M×N filas (filas de origen multiplicadas por el recuento de atributos) antes del filtrado, lo que los candidatos temen que degrade el rendimiento en grandes conjuntos de datos. Sin embargo, los optimizadores modernos basados en costos reconocen la dependencia de datos de la expresión CASE en la pequeña tabla constante y a menudo transforman el plan en una simple proyección o operador físico UNPIVOT internamente, evitando la multiplicación real de filas. A diferencia del UNPIVOT nativo, que típicamente elimina resultados NULL automáticamente, este método requiere una cláusula WHERE explícita para descartar filas donde el atributo de origen fue NULL, de lo contrario, el conjunto de resultados contiene hechos vacíos espúreos que corrompen los cálculos agregados a posteriori.