【第153回】 SQL を使って縦持ちのデータを横持ちのデータに変換する方法
Salesforce Marketing Cloud のデータエクステンションに格納されているデータにおいて「縦持ちのデータを横持ちのデータに変換したい」場合の SQL の書き方を記事にしたいと思います。
※ Marketing Cloud SQL は「SQL Server」の機能に基づきます。
今回の事例としては、購読者が飼っているペットごとのデータが格納されている「Pet_Data」データエクステンションの中から、ペット名の後ろに、それぞれ「ちゃん」を付けて、カンマ区切りの横持ち表示をさせてみます。
さらに、犬と猫で分けて表示を行い、デフォルト値として犬側が NULL の場合は「ワンちゃん」、猫側が NULL の場合は「ネコちゃん」と入力します。
まず、Pet_Data データエクステンションの中身は下記のような形とします。
この登録の状況を整理しておきますと、下記のような形となっています。
Contact_1 は、犬が2頭 登録されている
Contact_2 は、犬が1頭 と 猫が1頭 登録されている
Contact_3 は、猫が2頭 登録されている
これを変換して、最終的には下記のように格納したいと思います。
では、今回の SQL クエリは以下のような形となります。少し理解しづらいかもしれませんが、上のデータエクステンションの項目名と照らし合わせながら、コードを読み解いてみてください。
SELECT DISTINCT a.Id AS [Id],
CASE WHEN LEFT(Dog.Name, LEN(Dog.Name) - 1) IS NULL THEN 'ワンちゃん' ELSE LEFT(Dog.Name, LEN(Dog.Name) - 1) END AS [Dog_Name],
CASE WHEN LEFT(Cat.Name, LEN(Cat.Name) - 1) IS NULL THEN 'ネコちゃん' ELSE LEFT(Cat.Name, LEN(Cat.Name) - 1) END AS [Cat_Name]
FROM Pet_Data a
CROSS APPLY (
SELECT DISTINCT CONCAT(b.Name, 'ちゃん') + '、'
FROM Pet_Data b
WHERE a.Id = b.Id
AND b.Animal_Type = '犬'
FOR XML PATH('')
) Dog(Name)
CROSS APPLY (
SELECT DISTINCT CONCAT(b.Name, 'ちゃん') + '、'
FROM Pet_Data b
WHERE a.Id = b.Id
AND b.Animal_Type = '猫'
FOR XML PATH('')
) Cat(Name)LEFT(Pet.Name, LEN(Pet.Name) - 1) の箇所は、最後に付いてしまう余計な「、」を削除する働きをしています。
今回のように、名前の後ろに「ちゃん」をつけたり、犬と猫のような出し分けが無ければ、このコードはもっとシンプルになります。汎用的なコードサンプルとして、以下を記載しておきます。XXX の部分はそのままでも良いですし、分かりやすい名前に変更しても OK です。
SELECT DISTINCT a.Id,
LEFT(XXX.FieldName, LEN(XXX.FieldName) - 1) as [FieldName]
FROM DataExtensionName a
CROSS APPLY (
SELECT DISTINCT b.FieldName + '、'
FROM DataExtensionName b
WHERE a.Id = b.Id
FOR XML PATH('')
) AS XXX(FieldName)
ちなみに、カンマ区切りの横持ちのデータを縦持ちに変換する SQL をお探しの場合は、Salesforce MVP のズザンナ・ヤルチンスカさんのブログにそのサンプルコードが記載されています。記載されている 2 つの解決策のうち、「Solution 2: Cross Apply with XML」の方が使い易いとは思います。
今回は以上です。