SQLSERVER　複数行のデータをカンマ区切りで取得したい（GROUP

前提・実現したいこと

以下のようなテーブルがあり、出身と性別で集約してそこに含まれるデータ数（人数）を取得し、
さらに最終的に別処理で利用するため集約したデータのDataIdを取得したいです。
My SQLやORACLEではGROUP_CONCATで実現可能な機能ですがSQLSERVERのため使用できず詰まっています。
環境としてはVB.NETからSQL Serverに接続しています。

|DataId|出身|性別|氏名|
|:--|:--:|--:|
|001|東京|男|田中|
|002|東京|男|山本|
|003|東京|女|佐藤|
|004|大阪|男|鈴木|
|005|大阪|女|斉藤|
|006|大阪|女|前田|
|007|愛知|女|小西|
|008|愛知|女|佐々木|

欲しいのは以下の形です。

|出身|性別|人数|DataId|
|:--|:--:|--:|
|東京|男|2|001,002|
|東京|女|1|003|
|大阪|男|1|004|
|大阪|女|2|005,006|
|愛知|女|2|007,008|

過去ログを参照してコードを組みましたがうまくいきません。
コード自体に間違いがあれば教えていただきたいです。
また他の方法で実現可能であれば方法を教えていただけませんでしょうか。
よろしくお願いします。

発生している問題・エラーメッセージ

「データベースアクセス中に致命的なエラーが発生しました。
　キーワード'FROM'付近に不適切な構文があります。」

該当のソースコード

SQLSERVER
1 SELECT 
2  tA.出身
3 ,tA.性別
4 ,COUNT(tA.氏名) AS 人数
5 ,(SELECT tB.DataId + ","
6   FROM テーブル１ AS tB
7   WHERE tB.DataId = tA.DataId
8   FOR XML PATH('')
9   ) AS DataId
10 FROM テーブル１ AS tA
11 GROUP BY tA.出身,tA.性別

試したこと

過去ログや他サイトなど参照し以下でできそうだったので実装しましたが
上記のエラーで詰まってしまっています。
過去ログ
↓過去ログにあったリンク先
参照サイト１
 参照サイト２

追加情報

hihijijiさんのアドバイスを受け、以下の通り修正したところエラー出ず動きました。
（","　を　','に修正し、DataIdがint型なのでstr型にキャストしました。）
ただ、実行結果が想定と異なっている状態で、引き続き困っております。

現在の実行結果
|出身|性別|人数|DataId|
|:--|:--:|--:|
|東京|男|2|001,002,003,004,005,006,007,008|
|東京|女|1|001,002,003,004,005,006,007,008|
|大阪|男|1|001,002,003,004,005,006,007,008|
|大阪|女|2|001,002,003,004,005,006,007,008|
|愛知|女|2|001,002,003,004,005,006,007,008|

全てのDataIdを連結してしまっている状態です。
自分自身とテーブルでサブクエリを組んでるので当たり前の結果のような気もしていますが
どのようにすればほしい結果が得られるのか考えつかない状態です。
クエリの組み方や他の方法などあればアドバイスいただけませんでしょうか。
よろしくお願いします。

該当のソースコード（hihijijiさんのアドバイス後）

SELECT
tA.出身
,tA.性別
,COUNT(tA.氏名) AS 人数
,(SELECT STR(tB.DataId) + ','
FROM テーブル１ AS tB
WHERE tB.DataId = tA.DataId
FOR XML PATH('')
) AS DataId
FROM テーブル１ AS tA
GROUP BY tA.出身,tA.性別

Orlofsky

2019/04/12 02:18

URLは https://teratail.com/help#about-markdown の [リンク] に修正してください。

Orlofsky

2019/04/12 02:20

>過去ログや他サイトなど参照し以下でできそうだったので実装しましたが >うまくいきませんでした。その内容やエラーメッセージも質問に追記されては？

Orlofsky

2019/04/12 02:20

質問に CREATE TABLE と INSERT を https://teratail.com/help#about-markdown の[コードを入力] で載せると適切な回答が付き易いです。

行動規範の内容に同意します

回答1件

ベストアンサー

パッと見ですが,(SELECT tB.DataId + ","ここです。
SQL Server では原則「'」シングルクォーテーションを使います。
実行環境やドライバにもよりますが

投稿2019/04/12 02:37

hihijiji

総合スコア4152

agawa

2019/04/12 03:59

アドバイスありがとうございます。いただいたアドバイスをヒントにして以下にように書き直しました。言葉足らずですみませんが、DataIdはint型だったのでstr型にしています。 SELECT tA.出身 ,tA.性別 ,COUNT(tA.氏名) AS 人数 ,(SELECT STR(tB.DataId) + ',' FROM テーブル１ AS tB WHERE tB.DataId = tA.DataId FOR XML PATH('') ) AS DataId FROM テーブル１ AS tA GROUP BY tA.出身,tA.性別上記で実行したところ、エラーが出ず実行できました。ありがとうございます。ただ、実行結果が欲しい結果と異なってしまい困っています。具体的には、以下のように全てのDataIdが連結されてしまいます。出身性別人数 DataId 東京男 2 001,002,003,004,005,006,007,008 東京女 1 001,002,003,004,005,006,007,008 大阪男 1 001,002,003,004,005,006,007,008 大阪女 2 001,002,003,004,005,006,007,008 愛知女 2 001,002,003,004,005,006,007,008 自分自身とテーブルでサブクエリを組んでるので当たり前の結果のような気もしていますがどのようにすればほしい結果が得られるのか考えつかない状態です。引き続き自分でも考えますが、クエリの組み方や他の方法などあればアドバイスいただけませんでしょうか。よろしくお願いします。