このようなクエリ組めますか？

Question

テーブルA

ID-A　　名前

1　　　　伊藤

2　　　　後藤

3　　　　加藤

テーブルB

ID-B　　ID-A　購入番号　購入品

1　　　　1　　　1　　　　もも

2　　　　1　　　2　　　　なし

3　　　　1　　　3　　　　みかん

4　　　　2　　　1　　　　みかん

5　　　　3　　　1　　　　もも

6　　　　3　　　2　　　　みかん

作りたいクエリ

ID-A　　名前　　　購入品1　　　購入品2　　　購入品3

1　　　　伊藤　　　もも　　　　なし　　　　　みかん

2　　　　後藤　　　みかん

3　　　　加藤　　　もも　　　　みかん

名前ごとに購入品を番号順で最大3つまで表形式でクエリ抽出し、表示したいのですが

可能でしょうか。

ご教授の程宜しくお願いいたします。

Answer 1

他にもやり方があると思いますが、おそらく一番わかりやすいのは以下のSQLです。

select [ID-A], 名前,

(select 購入品 from (
                        select 購入品, DENSE_RANK() OVER(ORDER BY 購入番号)  順位
                        from dbo.テーブルB b
                        where b.[ID-A] = a.[ID-A]
                    ) tbl
                where 順位 = 1
) 購入品1,

(select 購入品 from (
                        select 購入品, DENSE_RANK() OVER(ORDER BY 購入番号)  順位
                        from dbo.テーブルB b
                        where b.[ID-A] = a.[ID-A]
                    ) tbl
               where 順位 = 2
) 購入品2,

(select 購入品 from (
                        select 購入品, DENSE_RANK() OVER(ORDER BY 購入番号)  順位
                        from dbo.テーブルB b
                        where b.[ID-A] = a.[ID-A]
                    ) tbl
               where 順位 = 3
) 購入品3

from dbo.テーブルA a
order by [ID-A]

＃購入品がnullになるのが嫌であれば、isnull関数を使って下さい。
＃前提条件がわかりませんが、上のSQLでは同じ人が同じ商品を複数買うと破綻するんじゃないかと思います。もう少し詳しい条件を提示されると、それによってSQLが変わります。

---

★良い回答には質問者は回答済みマークを、閲覧者は投票を！

Answer 2

PIVOTを使って書けます。

SELECT a.[ID-A], [名前], [1] AS 購入品1, [2] AS 購入品2, [3] AS 購入品3
FROM (SELECT [ID-A], [購入番号], [購入品] FROM [テーブルB]) AS b1 PIVOT (
  MIN([購入品])
  FOR [購入番号]
  IN ([1], [2], [3])
) AS b2
INNER JOIN [テーブルA] AS a ON a.[ID-A] = b2.[ID-A];

Answer 3

>trapemiya様

ありがとうございます。

ご教授いただいたようにこのSQLであれば私でも少し理解できました。

前提条件としては同じ購入品名が複数買う場合はあります。

購入品1　購入品2　購入品3

もも　　もも　　　もも

その場合は破綻してしまうのでしょうか。

よろしくお願いいたします。

Answer 4

佐祐理様

ありがとうございます。

PIVOTを使えればこれだけすっきりと書けるとは驚きです。

PIVOTについてはまず勉強することから始めてみます。

Answer 5

例えば、

ID-B　　ID-A　購入番号　購入品
1　　　　1　　　1　　　　もも
2　　　　1　　　2　　　　みかん
3　　　　1　　　3　　　　みかん
4　　　　1　　　4　　　　なし

だった場合、上記の私のSQLでは、

ID-A　　名前　　　購入品1　　　購入品2　　　購入品3
1　　　　伊藤　　　もも　　　　みかん　　　　みかん

になります。
これを、

ID-A　　名前　　　購入品1　　　購入品2　　　購入品3
1　　　　伊藤　　　もも　　　　みかん　　　　なし

にしたいのであれば、

select 購入品, DENSE_RANK() OVER(ORDER BY 購入番号)  順位
from dbo.テーブルB b
where b.[ID-A] = a.[ID-A]

を、以下のように変える必要があります。

select 購入品, DENSE_RANK() OVER(ORDER BY 購入番号)  順位
from 
(select 購入品, min(購入番号) 購入番号 from dbo.テーブルB b
 where b.[ID-A] = a.[ID-A]
 group by 購入品
) t

以上のSQLでサブクエリを多用していますが、サブクエリは今回の件に関わらず重要なテクニックです。ぜひ、発想の一つに加えて下さい。
なお、今回の件に関しては、佐祐理さんが書かれているようにPIVOTを使うのがお勧めですが、条件によっては動的にSQLを組み立てる必要があります。

---

★良い回答には質問者は回答済みマークを、閲覧者は投票を！

Answer 6

とりあえず、わかっている範囲内の条件であれば、PIVOTのSQLを動的に作成する必要はなく、勝手に佐祐理さんのSQLを修正させていただければ、以下のようになりますね。

SELECT a.[ID-A], [名前], [1] AS 購入品1, [2] AS 購入品2, [3] AS 購入品3
FROM (SELECT [ID-A], num, [購入品] FROM
			(select [ID-A], 購入品, ROW_NUMBER() OVER(PARTITION BY [ID-A] ORDER BY  min(購入番号)) num from dbo.テーブルB b
			 group by [ID-A], 購入品) t
 ) AS b1 PIVOT (
  MIN([購入品])
  FOR num
  IN ([1], [2], [3])
) AS b2
INNER JOIN [テーブルA] AS a ON a.[ID-A] = b2.[ID-A];

---

★良い回答には質問者は回答済みマークを、閲覧者は投票を！

Answer 7

trapemiya様

いつもありがとうございます。

今回の使用方法としては最初に回答いただきました購入品2も3もみかんとなる方がクエリとしては欲しい結果になります。

サブクエリはなんとなく勉強したつもりでしたが、全然使いこなしていないので、もっと練習してみます。

PIVOTについても合わせて教えていただきありがとうございます。

こちらの方も一緒に使いこなせるように頑張ってみます。

今後ともよろしくお願いいたします。