MySQL
Prepared Statementを使用した動的Un-Pivotテーブル

条件に基づいて列の動的セットのピボットを解除する

次の例は、BI /レポートの理由で、トランザクションデータをピボットされていないデータに変換しようとしているときに非常に便利です。ピボットされないディメンションには動的な列セットがあります。

この例では、生データテーブルにマークされた質問形式の従業員評価データが含まれていると仮定します。

生データテーブルは次のとおりです。

create table rawdata

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

rawdataテーブルは、ETLプロシージャの一部としての一時テーブルであり、さまざまな数の質問を持つことができます。目標は、任意の数の質問、つまり、未旋回になる列に対して同じ非旋回手順を使用することです。

以下は、rawdataテーブルのおもちゃの例です：

データをunpivotするためのよく知られている静的な方法は、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

条件に従って列を選択し、準備されたステートメントを作成することは、データを動的にピボット解除する効率的な方法です。