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

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

新規登録して質問してみよう
ただいま回答率
85.50%
SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

2回答

2695閲覧

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

tenyan

総合スコア8

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2018/09/02 01:33

編集2018/09/02 12:49

前提・実現したいこと

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

male_namegroupid1female_namegroupid2
tarou001hanako101
jirou002hiroko102
jirou002aiko102
manabu003keiko102
manabu003tomomi103
naoto004tomomi103
takashi003tomomi103
takashi003yumi104
::::

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

male_countmale_namefemale_countfemale_name
1tarou,0011hanako,101
1jirou,0022hiroko,102,aiko,102
3manabu,003,naoto,004,takashi,0033keiko,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です。
宜しくお願い致します。

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sazi

2018/09/02 11:53

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

2018/09/02 12:19

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

2018/09/02 12:23

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

2018/09/02 12:50

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

回答2

0

ベストアンサー

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

SQL

1with step1 as ( 2select 'M' as type, male_name as key_name, '' as pkey_name, groupid1 as key_groupid from 元データ 3union all 4select 'M' as type, male_name as key_name, female_name as pkey_name, groupid1 as key_groupid from 元データ 5union all 6select 'F' as type, female_name as key_name, '' as pkey_name, groupid2 as key_groupid from 元データ 7union all 8select 'F' as type, female_name as key_name, male_name as pkey_name, groupid2 as key_groupid from 元データ 9) 10, step2 as( 11select 0 as level, step1.key_name as top_Key 12 , cast(step1.key_name as varchar(100)) as all_group 13 , cast(case when type='M' then step1.key_name else '' end as varchar(100)) as mail_group 14 , cast(case when type='F' then step1.key_name else '' end as varchar(100)) as femail_group 15 , * 16from step1 17union all 18select level+ 1, step2.top_Key 19 , cast(all_group + ' > ' + step1.key_name as varchar(100)) 20 , cast(mail_group + case when step1.type='M' then case when mail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100)) 21 , cast(femail_group + case when step1.type='F' then case when femail_group<>'' then ',' else '' end + step1.key_name else '' end as varchar(100)) 22 , step1.* 23from step2 inner join step1 on step2.key_name=step1.pkey_name 24where step2.pkey_name<>step1.key_name 25) 26select distinct * from step2 as target 27where pkey_name<>'' and mail_group<>'' and femail_group<>'' 28order by all_group

DATA

1CREATE TABLE 元データ 2 ([male_name] varchar(7), [groupid1] varchar(3), [female_name] varchar(6), [groupid2] varchar(3)) 3; 4 5INSERT INTO 元データ 6 ([male_name], [groupid1], [female_name], [groupid2]) 7VALUES 8 ('tarou', '001', 'hanako', '101'), 9 ('jirou', '002', 'hiroko', '102'), 10 ('jirou', '002', 'aiko', '102'), 11 ('manabu', '003', 'keiko', '102'), 12 ('manabu', '003', 'tomomi', '103'), 13 ('naoto', '004', 'tomomi', '103'), 14 ('takashi', '003', 'tomomi', '103'), 15 ('takashi', '003', 'yumi', '104') 16;

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

投稿2018/09/02 13:23

編集2018/09/03 02:13
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

tenyan

2018/09/04 14:05

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

0

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

SQL CLR を使ってみた

投稿2018/09/02 01:56

Zuishin

総合スコア28656

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

tenyan

2018/09/02 11:06

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

2018/09/02 11:18

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

2018/09/02 11:28

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

2018/09/02 12:21

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問