【SQL Server】縦持ちのデータを区切り文字で1列のデータとして横持ちで取得する方法

以下のような、PKがIDとSUB_IDで、食材名を保持している食材マスタというテーブルがあるとします。

これを以下のように区切り文字(今回はカンマ区切り)でIDごとに1列で横持ち取得するようにする方法です。

やり方

SQL Server 2016以降

便利なSTRING_AGGを使用すれば引数の2つ目を区切り文字として連結してくれます。

SELECT
    ID
    , STRING_AGG(食マ.食材名, ',') AS 食材名配列 
FROM
    食材マスタ 食マ 
GROUP BY
    ID

SQL Server 2016以前（2016以降でも使える）

FOR XML PATH (”)を使用して、連結します。
そして、それだけだと、先頭のカンマが残ってしまうので、STUFF関数を使って削除しています。
※ちなみに、5行目を「SELECT DISTINCT」とすれば、食材名が重複している場合は重複削除されます。STRING_AGGのほうで重複削除すると、GROUP BYとかしたりすることになって少々複雑なので、重複削除の必要がある場合はこのやり方の方が簡単かも。

SELECT
    ID
    , STUFF( 
        ( 
            SELECT
                ',' + 食マ2.食材名 
            FROM
                食材マスタ 食マ2 
            WHERE
                食マ2.ID = 食マ1.ID FOR XML PATH ('')
        ) 
        , 1
        , 1
        , ''
    ) AS 食材名配列 
FROM
    食材マスタ 食マ1 
GROUP BY
    ID