見出し画像
Photo by daisaw33

【第153回】 縦持ちのデータをカンマ区切りの横持ちに変換する SQL

Nobuyuki Watanabe

Salesforce Marketing Cloud のデータエクステンションに格納されているデータにおいて「縦持ちのデータを横持ちに変換したい」場合の SQL の書き方を記事にしたいと思います。

※「縦持ちのデータを横持ちに変換する SQL」と WEB で検索すれば、同じような SQL のサンプルがいくつか見つかるかもしれませんが、それが実際に Marketing Cloud で使用できるかは、試してみないと分かりません。私が今回紹介するものを Marketing Cloud で使用可能な一例としてください。

今回の事例としては、購読者が飼っているペットごとのデータが格納されている「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 のズザンナ・ヤルチンスカさんのブログにそのコードが記載されています。２つの解決策のうち、「Solution 2: Cross Apply with XML」の方がスッキリしてて使いやすいと思います。参考までに。

今回は以上です。


次の記事はこちら

前回の記事はこちら

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

この記事が気に入ったらサポートをしてみませんか？