カラムの値からカンマ区切り (CSV) の文字列を生成する

カラムの値からカンマ区切り (CSV) の文字列を生成するには？

SQL のスクリプトで、複数行のカラムの値から、1 つのカンマ区切り (CSV) の文字列を生成したいような時ありますよね。

例えば以下のような感じです。

FOR XML 句を使って、カラムの値からカンマ区切り (CSV) の文字列を生成する方法

カンマ区切り (CSV) の文字列を生成する方法はいろいろありますが、今回は FOR XML 句を使ったやり方のひとつをご紹介します。

FOR XML 句を SQL クエリーに指定すると、SQL クエリーの結果を XML 形式で取得することができます。

では実際に、カンマ区切り (CSV) の文字列を作ってみましょう。

例えば、以下のような Groups　マスターがあったとします。

そして、誰がそのグループに属するかが保存されている、以下のような GroupMembers テーブルがあったとします。

通常はこのような場合、Members マスターがあり、そのキーの情報を使うほうが良いですが、ここではシンプルにするために直接名前を値として保存しています。

ここからが本題です。上記のような Group マスターの情報 + そのメンバーの名前をカンマ区切り (CSV) で取得するクエリーは以下の通りです。

SELECT   G.GroupCode,
         G.GroupName,
         REPLACE( (SELECT MemberName AS [data()]
                   FROM GroupMembers
                   WHERE GroupCode = G.GroupCode
                   ORDER BY MemberName
                   FOR XML PATH ('')), ' ', ',') AS MemberNames
FROM     Groups AS G;

FOR XML 句の使い方は、また他の記事でご紹介しますが、ここでのポイントはカンマ区切り (CSV) の文字列にしたいカラムの値を取得するクエリーに PATH モードの FOR XML 句を指定して、スペース区切りの文字列を生成し、スペースを REPLACE を使ってカンマに置き換えています。

CSV にしたいカラム値にスペースがある時は、REPLACE を使わずに以下のようにしても良いと思います。

1 文字目のカンマを取り除きたいだけなので、 STUFF を使っても OK です。

SELECT   G.GroupCode,
         G.GroupName,
         STUFF( (SELECT ',' + MemberName
                   FROM GroupMembers
                   WHERE GroupCode = G.GroupCode
                   ORDER BY MemberName
                   FOR XML PATH ('')), 1, 1, '') AS MemberNames
FROM     Groups AS G;

ちなみに FOR XML PATH ('') の ('') は XML のエレメントが生成されないようにしているもので、それがないと以下のようになってしまいますので忘れないようにご注意を！

[ 追記 ]
SQL Server 2017 からは STRING_AGG 関数を使って簡単にカンマ区切り (CSV) の文字列が生成できます。

詳しくはこちらのページをご覧ください。
=> SQL Server 2017 新機能 - STRING_AGG 関数