T-SQL: 複数行のカラムの値からひとつの文字列を生成する方法

複数行のカラムの値からひとつの文字列を生成する方法

SQL のスクリプトで、複数行のカラムの値から、カンマ区切り (CSV) や改行を入れて、ひとつの文字列を生成したいような時ありませんか。

いろんなやり方があると思いますが、複数行のカラムの値からひとつの文字列を生成する方法をひとつご紹介します。

以下のような Student テーブル があります。

ここから FirstName と LastName 順で連番をふって並べ替え、改行を入れてつなげた、以下のようなひとつの文字列を取得したいとします。

複数行をつなげて、ひとつの文字列にするには、FOR XML PATH を使ったり、STRING_AGG() 関数を使ったり、いろんな方法がありますが、ここでは COALESCE() 関数を使います。

FirstName と LastName 順で連番をふって並べ替え、改行でつなげてひとつの文字列を取得するクエリーは以下の通りです。

DECLARE @StudentInfo NVARCHAR(MAX); 

SELECT	@StudentInfo = COALESCE(@StudentInfo + CHAR(13) + CHAR(10), '') 
			+ CAST(ROW_NUMBER() OVER (ORDER BY FirstName, LastName) AS NVARCHAR(20))
			+ ': '
			+ ISNULL(FirstName, '') 
			+ ' ' 
			+ ISNULL(LastName, '')
FROM	Student
ORDER BY FirstName,
	 LastName;

SELECT @StudentInfo;

COALESCE() 関数をつかって、@StudentInfo に一行分の文字列 + 改行(CR/LF) を、順番に行数分追加していくようにして、ひとつの文字列につなげています。

一行分の文字列は 連番: FirstName LastName です。

連番は ROW_NUMBER() で FirstName, LastName 順に連番をふって、それを NVARCHAR(20) にキャストしています。

文字列を + でつなげる時は、ひとつでも NULL が全体が NULL になってしまうので、NULL になる可能性のある時はお気をつけください。

このクエリーを SQL Server Management Studio で実行しますが、デフォルトの結果をグリッドに表示する方法では、改行が入っているのがわかりにくいので、結果をテキストに表示するように変更してクエリーを実行します。

@StudentInfo に 連番: FirstName LastName が改行でつながった、文字列が入っているのがわかります。

カンマ区切りでつなげたい時は CHAR(13) + CHAR(10) の部分を ',' に変更すれば取得できます。

DECLARE @StudentInfo NVARCHAR(MAX); 

SELECT	@StudentInfo = COALESCE(@StudentInfo + ',', '') 
			+ CAST(ROW_NUMBER() OVER (ORDER BY FirstName, LastName) AS NVARCHAR(20))
			+ ': '
			+ ISNULL(FirstName, '') 
			+ ' ' 
			+ ISNULL(LastName, '')
FROM	Student
ORDER BY FirstName,
	 LastName;

SELECT @StudentInfo;

以上、複数行のカラムの値からひとつの文字列を生成する方法をご紹介しました。