SQL Serverで、取得した複数行の結果をカンマ区切りの文字列で１行にする

やりたかったこと

• SQL Serverでデータ取得したい
• ある項目で、同じ値が２件以上ある場合、そのデータだけを取得してカンマ区切りで１行で取得したい。

例）　こんな感じのテーブルの中から、

　　　番号
　　　———
　　　00001
　　　00001
　　　00002
　　　00003
　　　00003
　　　00003
　　　00004
　　　

　　こういう結果を取得したい。

　　　番号
　　　———
　　　00001, 00003

　　※重複がある番号のみをカンマ区切りで取得

SQL Server以外では、関数が用意されているDBもあるようです

• MySQLでは、GROUP_CONCAT()
• PostgreSQLでは、ARRAY_TO_STRING() 

こちらのブログがかなり役立ちそうでした。

しかしながら今回やりたいのはSQL Serverです。専用の関数はないようで、若干手こずったので備忘として。

SQL Serverでの場合、FOR XML PATHを利用します

以下を実行することで、想定通りの結果を取得できます。

SELECT STUFF((SELECT ',' + 番号
FROM テーブル名
GROUP BY 番号
HAVING COUNT(1) > 1
FOR XML PATH('')), 1, 1, '')

ちょっと詳しく

文字列の位置と文字数を指定して、特定の文字に置換する関数、STUFF。

STUFF(文字列, 開始位置, 文字数, 置換後文字)

取得結果をXML形式で１行１項目として取得する。

SELECT 項目名
FROM テーブル名 
FOR XML PATH('')

ここで項目名の指定に「 ‘,’ + 項目名」 とすると、「,00001,00003」のような形式で取得できる。

頭のカンマを削除するためにSTUFFをかませる。