SQL Serverで縦に並んだデータを横並びにカンマ区切りで取得したい、という処理がありました。
MySQLであればGROUP_CONCAT、OracleであればLISTAGGがありますがSQL Serverには同等の関数がなく詰んでいたのですが、SQL Serverの関数だけで実現できたのでまとめます。
（なお本内容はteratailにもお世話になりました。同内容の質問を投稿しているのは私です。）
やりたいこと
以下のようなテーブルがあるとします。
出身と性別で集約してそこに含まれるデータ数（人数）を取得し、
さらに最終的に別処理で利用するため集約したデータのDataIdを取得したい。
※DataIdはint型
| DataId | 出身 | 性別 | 氏名 |
|---|---|---|---|
| 1 | 東京 | 男 | 田中 |
| 2 | 東京 | 男 | 山本 |
| 3 | 東京 | 女 | 佐藤 |
| 4 | 大阪 | 男 | 鈴木 |
| 5 | 大阪 | 女 | 斎藤 |
| 6 | 大阪 | 女 | 前田 |
| 7 | 愛知 | 女 | 小西 |
| 8 | 愛知 | 女 | 佐々木 |
欲しいのは以下の形です。
| 出身 | 性別 | 人数 | DataId |
|---|---|---|---|
| 東京 | 男 | 2 | 1,2 |
| 東京 | 女 | 1 | 3 |
| 大阪 | 男 | 1 | 4 |
| 大阪 | 女 | 2 | 5,6 |
| 愛知 | 女 | 2 | 7,8 |
以下のコードで実現できました
結論から言うと、SQL ServerではFOR XML PATH(”) をつけたサブクエリを使うことで実現することができました。
なお前述の通りDataIdはint型でカンマと連結できないのでstr型にキャストしています。
SELECT
tA.出身
,tA.性別
,COUNT(tA.氏名) AS 人数
,(SELECT
STR(tB.DataId) + ','
FROM
テーブル１ AS tB
WHERE
tB.出身 = tA.出身 AND tB.性別 = tA.性別
FOR XML PATH('')
) AS DataId
FROM
テーブル１ AS tA
GROUP BY
tA.出身,tA.性別取得結果
| 出身 | 性別 | 人数 | DataId |
|---|---|---|---|
| 東京 | 男 | 2 | 1,2, |
| 東京 | 女 | 1 | 3, |
| 大阪 | 男 | 1 | 4, |
| 大阪 | 女 | 2 | 5,6, |
| 愛知 | 女 | 2 | 7,8, |
厳密には、DataId列の最後のデータの後ろにもカンマが入ってしまっているので当初実現したかったこととは異なりますがこれは処理上無視できるので問題ないとしています。
どうしても最後のカンマが気になる方はサブクエリ内の以下の部分を
STR(tB.DataId) + ','以下のように前後を入れ替えて
',' + STR(tB.DataId)さらにStuff関数で頭のカンマを消してあげると綺麗な結果が得られると思います。具体的には以下の通り。
SELECT
tA.出身
,tA.性別
,COUNT(tA.氏名) AS 人数
,STUFF(
(SELECT
',' + STR(tB.DataId)
FROM
テーブル１ AS tB
WHERE
tB.出身 = tA.出身 AND tB.性別 = tA.性別
FOR XML PATH(''))
,1,1,"") AS DataId
FROM
テーブル１ AS tA
GROUP BY
tA.出身,tA.性別取得結果
| 出身 | 性別 | 人数 | DataId |
|---|---|---|---|
| 東京 | 男 | 2 | 1,2 |
| 東京 | 女 | 1 | 3 |
| 大阪 | 男 | 1 | 4 |
| 大阪 | 女 | 2 | 5,6 |
| 愛知 | 女 | 2 | 7,8 |
FOR XML PATH について
FOR XML PATHはSQLの後ろに追加して使うことでSQL クエリの結果を XML 形式で取得することができます。
| DataId | 出身 | 性別 | 氏名 |
|---|---|---|---|
| 1 | 東京 | 男 | 田中 |
| 2 | 東京 | 男 | 山本 |
| 3 | 東京 | 女 | 佐藤 |
| 4 | 大阪 | 男 | 鈴木 |
| 5 | 大阪 | 女 | 斎藤 |
SELECT * FROM テーブル1 FOR XML PATH('row')<row>
<DataId>1</DataId>
<出身>東京</出身>
<性別>男</性別>
<氏名>田中</氏名>
</row>
<row>
<DataId>2</DataId>
<出身>東京</出身>
<性別>男</性別>
<氏名>山本</氏名>
</row>
<row>
<DataId>3</DataId>
<出身>東京</出身>
<性別女</性別>
<氏名>佐藤</氏名>
</row>
<row>
<DataId>4</DataId>
<出身>大阪</出身>
<性別>男</性別>
<氏名>鈴木</氏名>
</row>
<row>
<DataId>5</DataId>
<出身>大阪</出身>
<性別>女</性別>
<氏名>斎藤</氏名>
</row>このようなXML形式で結果が返ってきます。
Pathの引数にrowを渡したので<row></row>で返ってきましたが、空白(”)を引き渡すことで要素をなくすことができます。
SELECT * FROM テーブル1 FOR XML PATH('')<DataId>1</DataId>
<出身>東京</出身>
<性別>男</性別>
<氏名>田中</氏名>
<DataId>2</DataId>
<出身>東京</出身>
<性別>男</性別>
<氏名>山本</氏名>
<DataId>3</DataId>
<出身>東京</出身>
<性別女</性別>
<氏名>佐藤</氏名>
<DataId>4</DataId>
<出身>大阪</出身>
<性別>男</性別>
<氏名>鈴木</氏名>
<DataId>5</DataId>
<出身>大阪</出身>
<性別>女</性別>
<氏名>斎藤</氏名>ここで今回カンマ区切りのDataIdを取得するために書いたSQLを改めてみてみます。サブクエリのみを抜粋しています。
SELECT
STR(tB.DataId) + ','
FROM
テーブル１ AS tB
WHERE
tB.出身 = tA.出身 AND tB.性別 = tA.性別
FOR XML PATH('')このサブクエリをクエリとして置き換えてみます。サブクエリではないのでシンプルになります。取得結果は以下の通り。
| DataId | 出身 | 性別 | 氏名 |
|---|---|---|---|
| 1 | 東京 | 男 | 田中 |
| 2 | 東京 | 男 | 山本 |
| 3 | 東京 | 女 | 佐藤 |
| 4 | 大阪 | 男 | 鈴木 |
| 5 | 大阪 | 女 | 斎藤 |
| 6 | 大阪 | 女 | 前田 |
| 7 | 愛知 | 女 | 小西 |
| 8 | 愛知 | 女 | 佐々木 |
SELECT STR(tB.DataId) + ','
FROM テーブル1 AS tB
FOR XML PATH('')1,2,3,4,5,6,7,8,サブクエリでなければ上記の結果が返ってきます。
サブクエリではwhereで出身と性別がtAと一致しているデータと指定することで、取得したかったデータが実現できたということになります。
以上です。
参考サイト
以下のサイトを参考にさせていただいています。ありがとうございます。
