複数行を１行にまとめたい

Question

■メンバーテーブル

ID　名前　        住所

1     山田太郎    東京都港区…

2     山田次郎    東京都渋谷区…

3     山田三郎    東京都新宿区…

■学歴テーブル

ID　区分　  卒業年     学校名

1     1        2001    港区小学校

1     2        2004    港区中学校

1     3        2007    港区高校

2     1        2001    渋谷区小学校

2     2        2004    渋谷区中学校

2     3        2007    渋谷区町高校

3     1        2001    新宿区小学校

3     2        2004    新宿区中学校

3     3        2007    新宿区高校

としたとき、以下のように取得したいと思います。

■SELECT結果

ID　名前　        住所               卒業年1*   学校名1       卒業年2     学校名2        卒業年3       学校名3 

1   山田太郎    東京都港区…     2001  港区小学校     2004  港区中学校     2007  港区高校

2   山田次郎    東京都渋谷区…  2001  渋谷区小学校  2004   渋谷区中学校  2007  渋谷区高校

3   山田三郎    東京都新宿区… 2001  新宿区小学校   2004  新宿区中学校  2007  新宿区高校

 *=(区分)

ＳＱＬ：複数行を１レコードにしたい　等を参考にしてみましたが、上手くいきません。

どなたか御指導お願いします。

Answer 1

どのように書かれてみましたか？　学歴なので上限が決まっているなら列数が固定となり、リンク先で佐祐理さんが書かれているように列数固定のSQLを書けばさほど難しくないはずです。列数が決まっていないのであれば、私がリンク先で紹介しているように動的にPIVOTを組み立てた方が良いでしょう。
とりあえず上記のテーブルに対して書かれたSQLを見せていただけると、何らかのアドバイスができると思います。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 2

trapemiya さん

早速の回答ありがとうございます。

SELECT ID, [1], [2], [3]
FROM history
PIVOT (
    MIN(year)
    FOR div IN ([1], [2], [3])
) AS P

(history:学歴テーブル、year:卒業年、div:区分)

としたところ、

ID   1   2   3

1   NULL   NULL   2007

1   2001   NULL   NULL

1   NULL   2004   NULL

2   NULL   NULL   2007

2   2001   NULL   NULL

2   NULL   2004   NULL

3   NULL   NULL   2007

3   2001   NULL   NULL

3   NULL   2004   NULL

と出力された時点で、挫折…GROUP BY?ダメ。

まだ、メンバーテーブルの結合にも辿り着かないのに…

道程の遠さにすぐさま質問をしてしまいました。

記述を忘れていましたが、SQL Server 2012 にて、開発を行っています。

列数は固定で大丈夫です。

お付き合いよろしくお願いします。

Answer 3

クエリの問題では無さそうですね。

Answer 4

＞道程の遠さにすぐさま質問をしてしまいました。

了解しました。確かにちょっと遠そうですので、とりあえず例を以下に示します。これを元にPIVOTの勉強をしてみて下さいね。
列数は固定で大丈夫ということだったので、区分は焼き込んでいます。

SELECT ID, 名前, 住所, [1] 卒業年1, [2] 卒業年2, [3] 卒業年3,
 (select 学校名 from 学歴 where P.ID = ID and 区分 = 1) 学校名1,
 (select 学校名 from 学歴 where P.ID = ID and 区分 = 2) 学校名2,
 (select 学校名 from 学歴 where P.ID = ID and 区分 = 3) 学校名3
 FROM (select m.ID, m.名前, m.住所, g.卒業年, g.区分 from 学歴 g
                      inner join メンバー m on g.ID = m.id) as T
 PIVOT (
     MIN(卒業年)
     FOR 区分 IN ([1], [2], [3])
 ) AS P

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 5

aviator_さん

回答ありがとうございます、何度も試してみるんですが…上手くいかないんですよね。

データもクエリも確認しているつもりなんですけど…

Answer 6

trapemiyaさん

教えていただいたクエリでは確かに希望通りの結果が得られました。

PIVOTでは、１つの列（今回の例では「卒業年」)しか指定できないから、「学校名」については取得の仕方が違うのでしょうか。

私は「区分」で振り分けた複数列が都合良く取得できるものと勘違いしていました。

もう暫く試行錯誤を繰り返し、PIVOTの使い方を理解したいと思います。

ありがとうございました。

Answer 7

＞PIVOTでは、１つの列（今回の例では「卒業年」)しか指定できないから、「学校名」については取得の仕方が違うのでしょうか。

１つの列でしか指定できないのはその通りです。ですからできあがったPIVOTテーブルからselectする際にサブクエリで「学校名」を求めています。PIVOTテーブルを作成してしまえば、後は普通のテーブルと同じように扱ってselect文を発行するだけです。

＞私は「区分」で振り分けた複数列が都合良く取得できるものと勘違いしていました。

「区分」で振り分けるというか「区分」ごとに集計するものを一つしか指定できないので（今回の場合は「min(卒業年)」）、それ以外の列を生成することはできません。よって、上で書いたようにサブクエリで後から追加しています。

＞もう暫く試行錯誤を繰り返し、PIVOTの使い方を理解したいと思います。

試行錯誤される際は、SQL Server Management Studioのクエリ作成画面で試されると便利ですよ。インテリジェンスも効きますし、逆にインテリジェンスからここはこういう扱いになっているんだってこともわかります。私もPIVOTはこのようにして理解を深めました。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/