質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.12%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 729

tenyan

score 6

 前提・実現したいこと

下記のようなテーブルデータがあり、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です。
宜しくお願い致します。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • sazi

    2018/09/02 20:53

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

    キャンセル

  • tenyan

    2018/09/02 21:19

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

    キャンセル

  • sazi

    2018/09/02 21:23

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

    キャンセル

  • tenyan

    2018/09/02 21:50

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

    キャンセル

回答 2

checkベストアンサー

0

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をカーソルにした、読み飛ばしや加工などを行うテーブル型を返却するストアドとするのが良さそうな気がします。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/09/04 23:05

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

    キャンセル

0

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

SQL CLR を使ってみた

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/09/02 20:06

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

    キャンセル

  • 2018/09/02 20:18

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

    キャンセル

  • 2018/09/02 20:28

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

    キャンセル

  • 2018/09/02 21:21

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.12%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる