SQL Serverで配列的な集計ができるクエリを作成したい

Question

前提・実現したいこと

下記のようなテーブルデータがあり、1レコードの
male_nameとfemale_nameが組み合わせを意味するデータと仮定します。
（分かりやすくカップルのデータとします）
■元データ

male_name	groupid1	female_name	groupid2
tarou	001	hanako	101
jirou	002	hiroko	102
jirou	002	aiko	102
manabu	003	keiko	102
manabu	003	tomomi	103
naoto	004	tomomi	103
takashi	003	tomomi	103
takashi	003	yumi	104
:	:	:	:

これらのカップルデータを配列的なイメージで、付き合いが被っている人達を1レコードにまとめて、それに紐づくサブ情報も含めてカンマ区切りで抽出したいです。
※tomomiが、manabu、naoto、takashiとの組み合わせがあるので、manabuの付き合い全て、naoto、takashiの付き合いも全て1レコードにしたいです。
■クエリ結果

male_count	male_name	female_count	female_name
1	tarou,001	1	hanako,101
1	jirou,002	2	hiroko,102,aiko,102
3	manabu,003,naoto,004,takashi,003	3	keiko,102,tomomi,103,yumi,104

クエリ結果の1レコードの種類としては、male_count：female_countが
・1:1
・1:n
・n:m
・n:1
の4種類になるイメージです。
male_nameに紐づくfemale_nameの一覧（1:n）を抽出するクエリは作成してみたのですが、ここからどうしたらよいのかで悩んでおります。考え方だけでも構いませんので、教示いただければと思います。

データベースの環境は、SQL Server2008です。
宜しくお願い致します。

Comments

[sazi]

集約する前のSQLは出来てるんですか？

[tenyan]

「元データ」は別プログラムでのテキスト出力（csv）されたものをテーブル化したデータです。他の処理も含めてSQL Serverがメインにはなっているので、クエリで目的が達成できればと考えております。

[sazi]

それぞれの関係を表した１行に纏める前のSQLは出来ているのかとお尋ねしています。

[tenyan]

失礼しました。出来ておりません。投稿内容を編集させていただきました。

Answer 1

WITHを使用して再帰で出来そうな気がしますが、条件や編集をSQL内で組み立てるのが面倒です。
例えば以下のようなSQLで経路を辿ることはできます。
step1：親子関係の状態に正規化
step2：再帰

with step1 as (
select 'M' as type, male_name as key_name, '' as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'M' as type, male_name as key_name, female_name as pkey_name, groupid1 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, '' as pkey_name, groupid2 as key_groupid from 元データ
union all
select 'F' as type, female_name as key_name, male_name as pkey_name, groupid2 as key_groupid from 元データ
)
, step2 as(
select 0 as level, step1.key_name  as top_Key
     , cast(step1.key_name  as varchar(100)) as all_group
     , cast(case when type='M' then step1.key_name else '' end as varchar(100)) as mail_group
     , cast(case when type='F' then step1.key_name else '' end as varchar(100)) as femail_group
	 , * 
from step1
union all
select level+ 1, step2.top_Key
     , cast(all_group + ' > ' + step1.key_name as varchar(100)) 
     , cast(mail_group + case when step1.type='M' then case when mail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100)) 
     , cast(femail_group + case when step1.type='F' then case when femail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100)) 
     , step1.* 
from step2 inner join step1 on step2.key_name=step1.pkey_name
where step2.pkey_name<>step1.key_name
)
select distinct * from step2 as target
where pkey_name<>'' and mail_group<>'' and femail_group<>''
order by all_group

CREATE TABLE 元データ
    ([male_name] varchar(7), [groupid1] varchar(3), [female_name] varchar(6), [groupid2] varchar(3))
;
    
INSERT INTO 元データ
    ([male_name], [groupid1], [female_name], [groupid2])
VALUES
    ('tarou', '001', 'hanako', '101'),
    ('jirou', '002', 'hiroko', '102'),
    ('jirou', '002', 'aiko', '102'),
    ('manabu', '003', 'keiko', '102'),
    ('manabu', '003', 'tomomi', '103'),
    ('naoto', '004', 'tomomi', '103'),
    ('takashi', '003', 'tomomi', '103'),
    ('takashi', '003', 'yumi', '104')
;

上記はまだまだ見直ししないと駄目ですけど、上記のような再帰SQLをカーソルにした、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。

Comments

[tenyan]

回答ありがとうございます。
いただいた内容を踏まえて色々と検討してみたいと思います。

Answer 2

面倒なので C# でやればいいと思います。

SQL CLR を使ってみた

Comments

[tenyan]

回答ありがとうございます。
出来ればクエリのみで実現したいと考えております。

[Zuishin]

ストアドプロシージャを柔軟に作れる仕組みなのでクエリのみでアクセスできます。

[Zuishin]

あと制限・縛りがある時はその理由も書かないと他の人も答えづらいです。

[tenyan]

そうなんですね！SQL CLRについてもう少し勉強してみます。
アドバイスありがとうございます。