SQL Server 2017 新機能 - STRING_AGG 関数 - CSV 文字列生成が簡単に！

SQL Server 2017 新機能 - STRING_AGG 関数

2017 年 10 月に SQL Server 2017 がリリースされました。

SQL Server 2017 の新機能の中で、今回は便利そうな STRING_AGG 関数を使ってみます。

STRING_AGG 関数

STRING_AGG 関数は、区切り文字を指定して、複数行の値をつなげて、ひとつの文字列にしてくれる組み込み関数です。

今まで「カラムの値からカンマ区切り (CSV) の文字列を生成する」のページの記事のように XML を使うなど、いろいろ工夫して生成しなければならなかったカンマ区切りの文字列も、この関数で簡単に取得することができます。

STRING_AGG 関数の構文は次の通りです。

STRING_AGG ( expression, separator ) 
	[ WITHIN GROUP ( ORDER BY  [ ASC | DESC ] ) ]

expression には、つなげたいカラム名など使った文字列式、separator には区切り文字を指定します。

WITHIN GROUP はオプショナルで、値を並べる順番を指定することができますが、ひとつのクエリーにひとつだけしか指定できません。

複数の STRING_AGG をひとつのクエリーに使っていて、WITHIN GROUP をひとつの STRING_AGG に指定していても、全ての STRING_AGG の結果がその並び順になります。

それでは、STRING_AGG を使ってみましょう。次のような Student テーブルがあります。

※ サンプルデータを生成するスクリプトはこちらにありますが、同じ名前のテーブルが既に存在している場合、削除されてしまうのでご注意ください。

FirstName や LastName のカンマ区切りの文字列は次のように取得できます。

SELECT  STRING_AGG (FirstName, ',') WITHIN GROUP (ORDER BY FirstName) AS FirstNameCSV,
        STRING_AGG (LastName, ',') AS LastNameCSV,
        STRING_AGG (FirstName + '.' + LastName, ' || ') AS FullNameCSV
FROM    Student;

[ 実行結果 ]

ソート順は FirstNameCSV を生成する STRING_AGG にだけ設定されていますが、LastNameCSV も FullNameCSV も FirstName 順にデータが並んでいます。

また、FullNameCSV のように、区切り文字は一文字でなくても大丈夫です。

GROUP BY と一緒に使って、例えば性別 (Gender) ごとの、FirstName のカンマ区切りの文字列なども簡単に取得できます。

SELECT      Gender,
            STRING_AGG (FirstName, ',') WITHIN GROUP (ORDER BY FirstName) AS FirstNameCSV
FROM        Student
GROUP BY    Gender;

[ 実行結果 ]

とっても便利ですね！