根据条件取消动态一组动态列

Created: November-22, 2018

以下示例是一个非常有用的基础，当你尝试将事务数据转换为未转动的数据时，出于 BI /报告原因，其中要取消转动的维可以具有动态的列集。

对于我们的示例，我们假设原始数据表包含标记问题形式的员工评估数据。

原始数据表如下：

create table rawdata

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

rawdata 表是一个临时表，作为 ETL 过程的一部分，可以有不同数量的问题。目标是对任意数量的问题使用相同的非旋转过程，即将要取消旋转的列。

下面是 rawdata 表的玩具示例：

StackOverflow 文档

在 MYSQL 中使用 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;

在我们的例子中，我们想要定义一种方法来取消任意数量的 QuestionId 列。为此，我们需要执行一个准备好的语句，它是所需列的动态选择。为了能够选择需要取消旋转的列，我们将使用 GROUP_CONCAT 语句，我们将选择数据类型设置为 int 的列。在 GROUP_CONCAT 中，我们还包括要执行的 SELECT 语句的所有其他元素。

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;

在另一种情况下，我们可以选择列名称与模式匹配的列，例如，而不是

DATA_TYPE = 'Int'

使用

COLUMN_NAME LIKE 'Question%'

或者可以通过 ETL 阶段控制的合适的东西。

准备好的声明最终定稿如下：

set @temp3 = null;

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

select @temp3;

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

unpivoteddata 表如下：

SELECT * FROM unpivoteddata

StackOverflow 文档