SQL Server2017で追加されたSTRING_AGG関数はPIVOTと相性が良い

こういう感じ↓のいわゆる「縦持ち」のデータを

こういう感じ↓のいわゆる「横持ち」(クロス表、ピボットテーブル)表示したいという需要は多くあります。

ExcelやBIツールなどを使えばドラッグアンドドロップでほーいっと簡単にできますが、SQLServer単体でやってしまった方が良い状況もたまにあります。

上記の縦持ちのデータを取得したSELECT文が下記のようなSQLの場合、

SELECT 
    [YearMonth]
    ,[Item]
    ,[Sales]
FROM [dbo].[Sales]

PIVOT句を利用することで横持ち(クロス表、ピボットテーブル))形式に変更できます。

SELECT
    pvt.YearMonth
    , ISNULL(Apple, 0) as Apple
    , ISNULL(Banana, 0) as Banana
    , ISNULL(Cantaloupe, 0) as Cantaloupe
    , ISNULL(Durian, 0) as Durian
FROM [dbo].[Sales] as s
PIVOT (
    SUM(s.[Sales])
    FOR s.[Item] IN (Apple, Banana, Cantaloupe, Durian)
) as pvt

しかし、めんどうくさいことにFOR s.[Item] IN (Apple, Banana, Cantaloupe, Durian)の部分はs.[Item]カラムの中のデータを記述する必要があり、1つや2つならともかく数が多くなると記述するのも一苦労です。
そのため動的SQLを作成するパターンが多いようです。

PIVOTを良い感じに処理する動的SQL

DECLARE @sql_command_pivot_list NVARCHAR(MAX) = '';
DECLARE @sql_command_pivot_column_list NVARCHAR(MAX) = '';

SELECT
  @sql_command_pivot_list = @sql_command_pivot_list + '[' + s.Item + '],'
 ,@sql_command_pivot_column_list = @sql_command_pivot_column_list + 'ISNULL([' + s.Item + '],0) as [' + s.Item + '],' 
FROM dbo.Sales as s
GROUP BY s.Item
ORDER BY s.Item

SET @sql_command_pivot_list = SUBSTRING(@sql_command_pivot_list, 1, LEN(@sql_command_pivot_list) -1)
SET @sql_command_pivot_column_list = SUBSTRING(@sql_command_pivot_column_list, 1, LEN(@sql_command_pivot_column_list) -1)

DECLARE @sql_command NVARCHAR(MAX)

SET @sql_command = '
SELECT pvt.[YearMonth],' + @sql_command_pivot_column_list + '
FROM [dbo].[Sales] as s
PIVOT (
  SUM(s.[Sales])
  FOR s.[Item] IN (' + @sql_command_pivot_list + ')
) as pvt
'

PRINT @sql_command

EXEC sp_executesql @sql_command;

また、作成される@sql_command(PRINT @sql_command)は下記のようになります。

SELECT pvt.[YearMonth],ISNULL([Apple],0) as [Apple],ISNULL([Banana],0) as [Banana],ISNULL([Cantaloupe],0) as [Cantaloupe],ISNULL([Durian],0) as [Durian]
FROM [dbo].[Sales] as s
PIVOT (
  SUM(s.[Sales])
  FOR s.[Item] IN ([Apple],[Banana],[Cantaloupe],[Durian])
) as pvt

また、ブログ記事では横着していますがSQLを文字列で組み立てる際はSQLインジェクションに注意してください。

STRING_AGG関数を用いる

SQL Server2017にて追加されたSTRING_AGG関数を用いることで、上記のSQLをもう少しシンプルにできます。
STRING_AGG関数は、C#のstring.JOINメソッドのようにセパレーターを用いて文字列をする関数です。PostgreSQLでは以前からあったようですね。

docs.microsoft.com

STRING_AGG関数を用いて上記のSQLを書き直すと下記のようになります。

DECLARE @sql_command_pivot_list NVARCHAR(MAX);
DECLARE @sql_command_pivot_column_list NVARCHAR(MAX);

SELECT
  @sql_command_pivot_list = STRING_AGG('[' + s.Item + ']', ',')
 ,@sql_command_pivot_column_list = STRING_AGG('ISNULL([' + s.Item + '],0) as [' + s.Item + ']', ',') 
FROM dbo.Sales as s
GROUP BY s.Item
ORDER BY s.Item

DECLARE @sql_command NVARCHAR(MAX)
...

初期化時の空文字初期化が不要になりました。また、SELECT文がスッキリしました。そして末尾の,削除が不要になりました。スッキリ。