複数のレコードの値を区切り文字で連結する／Salesforce Marketing Cloud

複数レコードの値を区切り文字（例えば、カンマなど）で連結する SQL を仕事で使う時があったので、備忘録としてシェアします。Marketing Cloud はデータ加工ツールではないので、大規模なデータ加工は Marketing Cloud に取り込む前に行うのが原則ですが、私の場合は Personalization（旧：Interaction Studio）へのデータ連携でデータ加工が必要だったので、今回の SQL を作成しました。

どんなデータを作りたいのか

例えば、1 つの商品に対して複数のカテゴリがあって、カテゴリごとにレコードを持っている下記の状態があったとします。

1 つの商品に対して複数のカテゴリのレコード

その状態から商品 ID ごとにカテゴリを区切り文字で結合した下記のような状態に加工するのが今回の SQL の内容です。

商品 ID ごとにカテゴリをパイプで区切る

SQL の説明

SELECT
    PROD.ProductId,
    /* 2. 先頭の区切り文字を削除 */
    STUFF(
        (
　　　　　　　　　　　　　　　　　　　　　　　　/* 1. カテゴリをXML形式で取得 */
            SELECT
                '|' + Category /* '|'を変更すれば区切り文字を変更できる */
            FROM
                Product_de
            WHERE
                ProductId　= PROD.ProductId
            FOR XML PATH('')
        ),
		1, 1, ''
    ) AS Categories
FROM
    Product_de AS PROD

1. カテゴリを XML 形式で取得

カテゴリを XML 形式で取得します。XML はタグで囲まれた構造ですが、FOR XML PATH('') と指定するとタグが生成されず、データだけを横並びで取得することができます。サブクエリの内側のデータと外側のデータは商品 ID（ProductId）で紐づいています。

2. 先頭の区切り文字を削除

STUFF 関数で先頭の区切り文字を削除します。1 で取得したデータは、「|＋カテゴリ」の組み合わせが横並びになっていて、先頭に不要なパイプが入っています。

この SQL の使い道

冒頭でも書いた通り、Personalization のデータを Marketing Cloud で加工する時には使えると思います。もっと良い方法があるのかもしれませんが…。