MySQL
Tabla dinámica de Un-Pivot usando una declaración preparada

Des-pivote un conjunto dinámico de columnas basado en condición

El siguiente ejemplo es una base muy útil cuando intenta convertir datos de transacciones a datos no pivotados por motivos de BI / informes, donde las dimensiones que deben no pivotarse pueden tener un conjunto dinámico de columnas.

Para nuestro ejemplo, suponemos que la tabla de datos sin procesar contiene datos de evaluación de empleados en forma de preguntas marcadas.

La tabla de datos en bruto es la siguiente:

create table rawdata

(
 PersonId VARCHAR(255)
,Question1Id INT(11)
,Question2Id INT(11)
,Question3Id INT(11)
)  

La tabla rawdata es una tabla temporal como parte del procedimiento ETL y puede tener un número variable de preguntas. El objetivo es utilizar el mismo procedimiento de no giro para un número arbitrario de preguntas, es decir, columnas que no serán pivotadas.

A continuación se muestra un ejemplo de juguete de la tabla rawdata:

La forma bien conocida y estática de no particionar los datos, en MYSQL, es mediante el uso de UNION ALL:

create table unpivoteddata

(

 PersonId VARCHAR(255)
,QuestionId VARCHAR(255)
,QuestionValue INT(11)

);

INSERT INTO unpivoteddata SELECT PersonId, 'Question1Id' col, Question1Id 
FROM rawdata
UNION ALL
SELECT PersonId, 'Question2Id' col, Question2Id 
FROM rawdata
UNION ALL
SELECT PersonId, 'Question3Id' col, Question3Id 
FROM rawdata; 

En nuestro caso, queremos definir una manera de no dividir un número arbitrario de columnas QuestionId. Para eso necesitamos ejecutar una declaración preparada que es una selección dinámica de las columnas deseadas. Para poder elegir qué columnas deben ser no pivotadas, usaremos una declaración GROUP_CONCAT y elegiremos las columnas para las cuales el tipo de datos se establece en 'int'. En el GROUP_CONCAT también incluimos todos los elementos adicionales de nuestra instrucción SELECT que se ejecutará.

set @temp2 = null; 

SELECT GROUP_CONCAT(' SELECT ', 'PersonId',',','''',COLUMN_NAME,'''', ' col     ',',',COLUMN_NAME,' FROM rawdata' separator ' UNION ALL' ) FROM     INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'rawdata' AND DATA_TYPE = 'Int' INTO     @temp2;

select @temp2;

En otra ocasión podríamos haber elegido columnas con las que el nombre de la columna coincide con un patrón, por ejemplo, en lugar de

DATA_TYPE = 'Int'

utilizar

COLUMN_NAME LIKE 'Question%'

o algo adecuado que puede ser controlado a través de la fase ETL.

La declaración preparada se finaliza de la siguiente manera:

set @temp3 = null;

select concat('INSERT INTO unpivoteddata',@temp2) INTO @temp3;

select @temp3;

prepare stmt FROM @temp3;
execute stmt;
deallocate prepare stmt;

La tabla de datos no divididos es la siguiente:

SELECT * FROM unpivoteddata

La selección de columnas de acuerdo con una condición y luego la elaboración de una declaración preparada es una forma eficiente de desviar dinámicamente los datos.