【第153回】 SQL を使って縦持ちのデータを横持ちのデータに変換する方法

Nobuyuki Watanabe

2024年5月7日 08:22

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」の方が使い易いとは思います。

【第153回】 SQL を使って縦持ちのデータを横持ちのデータに変換する方法

次の記事はこちら

前回の記事はこちら

私の note のトップページはこちら