Think Twice
IT技術メモ | SQL Serverのメモ
Created: 2020-11-07 / Updated: 2022-10-27

SQL Serverで取得結果行を1列に連結するSQL(FOR XML PATH)

複数取得される結果を結合して1列で取得したい場合があります。SQL Serverでそれを行うSQLについてメモしておきます。

目次

下準備

こんなテーブルとデータがあったとします。

SQL
Copy
SELECT * FROM Table1 ORDER BY NO

取得結果

NO TEAM MEMBER
1 紅組 佐藤
2 紅組 鈴木
3 紅組 高橋
4 青組 田中
5 黄組 伊藤
6 黄組 渡辺

そしてこのテーブルから、こんな感じにチームごとのメンバーリストをCSV形式で出力したいとします。

取得結果（こんな感じを希望・・・）

TEAM MEMBER
紅組 佐藤,鈴木,高橋
青組 田中
黄組 伊藤,渡辺

さっそくやってみよう

さっそくやって行きましょう。SQL Serverでは、FOR XML PATHを使うことで実現することができます。

SQL
Copy
SELECT
    a.TEAM AS TEAM,
    (SELECT ',' + MEMBER FROM Table1 b WHERE b.TEAM = a.TEAM ORDER BY b.NO FOR XML PATH('')) AS MEMBER
FROM
    Table1 a

取得結果

TEAM MEMBER
紅組 ,佐藤,鈴木,高橋
紅組 ,佐藤,鈴木,高橋
紅組 ,佐藤,鈴木,高橋
青組 ,田中
黄組 ,伊藤,渡辺
黄組 ,伊藤,渡辺

いい感じになってきましたが、先頭の , が邪魔なのでSTUFF関数を使って除去してみましょう。
STUFF関数を組み込んでみるとこうなります。取得する先頭に,が付くので、それを除去しています。

SQL
Copy
SELECT
    a.TEAM AS TEAM,
    STUFF(
        (SELECT ',' + MEMBER FROM Table1 b WHERE b.TEAM = a.TEAM ORDER BY b.NO FOR XML PATH(''))
        , 1, 1, ''
    ) AS MEMBER
FROM
    Table1 a

取得結果

TEAM MEMBER
紅組 佐藤,鈴木,高橋
紅組 佐藤,鈴木,高橋
紅組 佐藤,鈴木,高橋
青組 田中
黄組 伊藤,渡辺
黄組 伊藤,渡辺

あともう一歩ですね。重複して出てきてしまっているデータをまとめてみましょう。

SQL
Copy
SELECT
    a.TEAM AS TEAM,
    STUFF(
        (SELECT ',' + MEMBER FROM Table1 b WHERE b.TEAM = a.TEAM ORDER BY b.NO FOR XML PATH(''))
        , 1, 1, ''
    ) AS MEMBER
FROM
    (SELECT DISTINCT TEAM FROM Table1) a
ORDER BY
    a.TEAM

取得結果

TEAM MEMBER
黄組 伊藤,渡辺
紅組 佐藤,鈴木,高橋
青組 田中

これで完成です。SQLはちょっとごちゃごちゃしますが、わりと簡単にできるみたいですね。

補足1 STUFF関数

STUFF関数は、引数を4つ取る関数で、指定した文字列(第1引数)の、指定した位置(第2引数)から、指定した文字数(第3引数)分を、置換文字列(第4引数)に置き換える関数です。以下のような感じになります。

STUFF関数
Copy
SELECT
    STUFF('abcdefg', 2, 3, '**new**') AS "結果";
結果
Copy
a**new**efg

補足2 SUBSTRING関数でもよい

先頭の,さえ除去できればいいので、以下のようにサブクエリにしてから、SUBSTRING関数を使っても可能ですね。

SUBSTRING関数, FROM句でサブクエリにする
Copy
SELECT
    TEAM,
    SUBSTRING(MEMBER, 2, LEN(MEMBER) - 1) AS MEMBER
FROM
    (
        SELECT
            a.TEAM AS TEAM,
            (SELECT ',' + MEMBER FROM Table1 b WHERE b.TEAM = a.TEAM ORDER BY b.NO FOR XML PATH('')) AS MEMBER
        FROM
            Table1 a
    ) as x
SUBSTRING関数, WITHでサブクエリにする
Copy
WITH X AS (
    SELECT
        a.TEAM AS TEAM,
        (SELECT ',' + MEMBER FROM Table1 b WHERE b.TEAM = a.TEAM ORDER BY b.NO FOR XML PATH('')) AS MEMBER
    FROM
        Table1 a
)
SELECT
    TEAM,
    SUBSTRING(MEMBER, 2, LEN(MEMBER) - 1) AS MEMBER
FROM
    X

補足3 FOR XML PATH

FOR XML PATHはSQLの後ろに付与して、XMLとして結果を取得するものです。
例えば以下のように全データを取得してみると…

SQL
Copy
SELECT * FROM Table1 FOR XML PATH('row')
取得結果
Copy
<row>
  <NO>1</NO>
  <TEAM>紅組</TEAM>
  <MEMBER>佐藤</MEMBER>
</row>
<row>
  <NO>2</NO>
  <TEAM>紅組</TEAM>
  <MEMBER>鈴木</MEMBER>
</row>
<row>
  <NO>3</NO>
  <TEAM>紅組</TEAM>
  <MEMBER>高橋</MEMBER>
</row>
<row>
  <NO>4</NO>
  <TEAM>青組</TEAM>
  <MEMBER>田中</MEMBER>
</row>
<row>
  <NO>5</NO>
  <TEAM>黄組</TEAM>
  <MEMBER>伊藤</MEMBER>
</row>
<row>
  <NO>6</NO>
  <TEAM>黄組</TEAM>
  <MEMBER>渡辺</MEMBER>
</row>

このようなXML形式で結果が返却されます。
注意としては、文字列型のように見えますが、あくまでXML型で取得されている点です。そのままでは通常の文字列のように加工できませんので、後ほど説明しますが、文字列型に変換してから利用します。

なお、PATHに'row'という文字列を渡していたので、各行を囲む要素がrowになっていました。こちらは変更できますので、例えば'hoge'にすれば、このようになります。

SQL
Copy
SELECT * FROM Table1 FOR XML PATH('hoge')
取得結果
Copy
<hoge>
  <NO>1</NO>
  <TEAM>紅組</TEAM>
  <MEMBER>佐藤</MEMBER>
</hoge>
<hoge>
  <NO>2</NO>
  <TEAM>紅組</TEAM>
  <MEMBER>鈴木</MEMBER>
</hoge>
<hoge>
  <NO>3</NO>
  <TEAM>紅組</TEAM>
  <MEMBER>高橋</MEMBER>
</hoge>
<hoge>
  <NO>4</NO>
  <TEAM>青組</TEAM>
  <MEMBER>田中</MEMBER>
</hoge>
<hoge>
  <NO>5</NO>
  <TEAM>黄組</TEAM>
  <MEMBER>伊藤</MEMBER>
</hoge>
<hoge>
  <NO>6</NO>
  <TEAM>黄組</TEAM>
  <MEMBER>渡辺</MEMBER>
</hoge>

そして、空文字を渡すことで、要素そのものをなくすこともできます。（構造崩壊）

SQL
Copy
SELECT * FROM Table1 FOR XML PATH('')
取得結果
Copy
<NO>1</NO>
<TEAM>紅組</TEAM>
<MEMBER>佐藤</MEMBER>
<NO>2</NO>
<TEAM>紅組</TEAM>
<MEMBER>鈴木</MEMBER>
<NO>3</NO>
<TEAM>紅組</TEAM>
<MEMBER>高橋</MEMBER>
<NO>4</NO>
<TEAM>青組</TEAM>
<MEMBER>田中</MEMBER>
<NO>5</NO>
<TEAM>黄組</TEAM>
<MEMBER>伊藤</MEMBER>
<NO>6</NO>
<TEAM>黄組</TEAM>
<MEMBER>渡辺</MEMBER>

さらに、取得する列名を失くすと、要素のタグを消失させることができます。（もはやXMLですらない）

SQL
Copy
SELECT
    NO + '',
    TEAM + '',
    MEMBER + ''
FROM
    Table1
FOR XML PATH('')
取得結果
Copy
1紅組佐藤2紅組鈴木3紅組高橋4青組田中5黄組伊藤6黄組渡辺

ここまでくれば、取得する項目を絞ったり、接続する文字を,や/などにしたりして、自由に文字列として加工できます。

さきほど、注意として書きましたが、XML型ではなく文字列型に変換する正式なやり方は、
TYPEディレクティブで受け取って、valueメソッドでNVARCHARに変換してあげるといいみたいです。
詳細は、以下の参考を参考にして下さい。

SQL
Copy
SELECT
    (SELECT
         NO + '',
         TEAM + '',
         MEMBER + ''
     FROM
         Table1
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

括弧の対応が分かりにくいですが、TYPEの後の括弧が、サブクエリの開始の括弧の閉じになります。
サブクエリの戻り(=XML型)を.valueでXPATHとして取得して、NVARCHARに変換しているようです。

取得結果は先ほどと同じになります。

取得結果
Copy
1紅組佐藤2紅組鈴木3紅組高橋4青組田中5黄組伊藤6黄組渡辺

補足4 他のデータベースでは

なお、MySQL(MariaDB) ではGROUP_CONCAT関数、OracleではLISTAGG関数、PostgreSQLではstring_agg関数で同様の処理が可能とのことです。

参考

関連メモ

参考サイト

𝕏 @fumokmm
©2024 Think Twice