次のように各レコードに対して数量を記録したテーブルがあります。
商品コード 数量
001 25
002 50
このテーブルから、1つのレコードに対する数量を一定数(たとえば20)以下に制限し、それを越える場合はレコードを分割した次のようなテーブルを作成したいです。
商品コード 数量
001_1 20
001_2 5
002_1 20
002_2 20
002_3 5
こうした変換をSQLのみで行うことは可能でしょうか? 環境としてはMS-ACCESSを考えていますが、一般的なSQLの話で構いません。
欲を言えば、分割後のレコードのコードが上記のように「分割前のレコードのコード+枝番」というようになっているとなお良いです。
よろしくお願いします。
単一テーブルだと思いつきませんね。
もし別テーブルを用意しておいてよいのであれば
[T_DUMMY]
[ID] [KEY] [ODD]
1-1 1 1
2-1 2 0
2-2 2 1
3-1 3 0
3-2 3 0
3-3 3 1
4-1 4 0
4-2 4 0
4-3 4 0
4-4 4 1
5-1 5 0
5-2 5 0
5-3 5 0
5-4 5 0
5-5 5 1
...
のようなテーブルを想定される総数を満たすように用意してやります。で、
SELECT T1.ID,T1.KEY,T2.IIF([ODD]=0,INT([数量計]/20),([数量計] MOD 20))
FROM [T_DUMMY] AS T1
INNER JOIN
(SELECT 商品コード,SUM([数量]) AS 数量計,INT(SUM([数量])/20)+1 AS KEY FROM [テーブル]) AS T2
ON T1.KEY=T2.KEY
という感じでどうでしょう。
ヘルプ見ないで書いてるので、AccessでMOD使えたっけとか、サブクエリ使えたっけというのはすみませんが調整してください。
考え方の参考です。
まあ、どうしてもこの処理をSQL一発でやれといわれたら、SQLServweでならカーソル、Accessでならレコードセット使います。
ありがとうございます。別テーブルを作成するのが少々、面倒ですが、想定される数量がそんなに大きくない場合は実用になりそうですね。
ちなみに1行目の
IIF([ODD]=0,INT([数量計]/20),([数量計] MOD 20))
の部分は、もしかしたら
IIF([ODD]=0,20,([数量計] MOD 20))
の方が正しいでしょうか?