Misc/SQL - a羅針盤

SQL †

SQL文のサンプルです。
DB（データーベース）はサンプルのSQL文を作成した時のDBでしか検証できていませんが、各DBは標準のSQLを元にDB固有の機能を追加して作成されているので他のDBでも使用可能の場合が多いと思います。

SQL内データ : VALUES (DB2,PostgreSQL) †

VALUESを使用することによりSQL文内でデータを持つことができます。この利点はDBのテーブルにアクセスしなくてもSQLを実行できることと、ちょっとしたテーブルをDBにテーブルを作成しなくてもSQL文内（メモリー内）にテーブルを作成して利用できることです。

↑

(1)構文 †

VALUESを使用する場合の構文は以下のようになります。テーブル名または項目名はSQL文の中で使用しない場合不要です。テーブル名とはVALUESで設定したデータをテーブルとして扱う時の名前になります。(データ,データ...)が１行のデータで複数の項目を表します。(),()...が複数の列のデータを表します。

( VALUES (データ,データ...),(データ,データ...)...) テーブル名(項目名1,項目名2...)

↑

(2)SQLで計算 †

例えば日付だけをSQLで計算したい場合以下のように何らかのテーブル(ここではSYSIBM.SYSDUMMY1でデータは１件)を使用してSQLを実行することで計算させることができますが、テーブルにアクセスするのは無駄です。

SELECT CURRENT TIMESTAMP + 1 DAY FROM SYSIBM.SYSDUMMY1

VALUESを使用すると以下のようになります。

SELECT CURRENT TIMESTAMP + 1 DAY FROM (VALUES(1))

又は

SELECT * FROM (VALUES(CURRENT TIMESTAMP + 1 DAY))

↑

(3)SQL文内データ †

複数項目(横)のデータは以下のようになります。

SELECT * FROM ( VALUES (2,3,4,5,6,7,8,9) ) XX(X2,X3,X4,X5,X6,X7,X8,X9)

複数行(縦)のデータは以下のようになります。

SELECT * FROM ( VALUES (2),(3),(4),(5),(6),(7),(8),(9) ) YY(Y)

3項目64行のデータは以下のようになります。

SELECT * FROM
     (
     VALUES (2,2,4),(2,3,6),(2,4,8),(2,5,10),(2,6,12),(2,7,14),(2,8,16),(2,9,18),
      (3,2,6),(3,3,9),(3,4,12),(3,5,15),(3,6,18),(3,7,21),(3,8,24),(3,9,27),
      (4,2,8),(4,3,12),(4,4,16),(4,5,20),(4,6,24),(4,7,28),(4,8,32),(4,9,36),
      (5,2,10),(5,3,15),(5,4,20),(5,5,25),(5,6,30),(5,7,35),(5,8,40),(5,9,45),
      (6,2,12),(6,3,18),(6,4,24),(6,5,30),(6,6,36),(6,7,42),(6,8,48),(6,9,54),
      (7,2,14),(7,3,21),(7,4,28),(7,5,35),(7,6,42),(7,7,49),(7,8,56),(7,9,63),
      (8,2,16),(8,3,24),(8,4,32),(8,5,40),(8,6,48),(8,7,56),(8,8,64),(8,9,72),
      (9,2,18),(9,3,27),(9,4,36),(9,5,45),(9,6,54),(9,7,63),(9,8,72),(9,9,81)
     ) KUKU(X,Y,XY)

↑

1行データの項目による複数行化 (DB2,PostgreSQL) †

前述のデータにより、九九の表は以下のSQLで8行8項目で表示されます。

SELECT X2||'x'||Y,X3||'x'||Y,X4||'x'||Y,X5||'x'||Y,X6||'x'||Y,X7||'x'||Y,X8||'x'||Y,X9||'x'||Y
FROM 
( SELECT * FROM (VALUES (2,3,4,5,6,7,8,9)) XX(X2,X3,X4,X5,X6,X7,X8,X9)) XXX,
( SELECT * FROM (VALUES (2),(3),(4),(5),(6),(7),(8),(9)) YY(Y) ) YYY

1     2     3     4     5     6     7     8
----- ----- ----- ----- ----- ----- ----- -----
2x2   3x2   4x2   5x2   6x2   7x2   8x2   9x2
2x3   3x3   4x3   5x3   6x3   7x3   8x3   9x3
2x4   3x4   4x4   5x4   6x4   7x4   8x4   9x4
2x5   3x5   4x5   5x5   6x5   7x5   8x5   9x5
2x6   3x6   4x6   5x6   6x6   7x6   8x6   9x6
2x7   3x7   4x7   5x7   6x7   7x7   8x7   9x7
2x8   3x8   4x8   5x8   6x8   7x8   8x8   9x8
2x9   3x9   4x9   5x9   6x9   7x9   8x9   9x9

　　　　　　　図1 九九組合わせ表
これにKUKUテーブルを追加して、九九の結果を表示するとなるとX2,X3...の各項目がKUKUテーブルへ異なった条件で一致した結果が必要なため各項目分のKUKUテーブル(8テーブル)をJOINして各テーブルへの条件文を記述する必要があります。
SELECT句は、

SELECT X2||'x'||YYY.Y||'='KUKU1.XY,X3||'x'||YYY.Y||'='KUKU2.XY...

FROM句はKUKUテーブルが実際のテーブルとすると、

KUKU AS KUKU1,KUKU AS KUKU2... <= 8テーブル記述

WHERE句は、

WHERE (XXX.X2=KUKU1.X AND YYY.Y=KUKU1.Y) AND (XXX.X3=KUKU2.X AND YYY.Y=KUKU2.Y)...  <= 8テーブル分記述

となり、かなり煩雑なSQL文になります。
そこでXXXテーブルの項目を列に分解して同じ処理をするようにします。

SELECT XXXX.X||'x'||YYY.Y||'='||KUKU.XY
 FROM
 ( SELECT CASE SEQ 
 WHEN 2 THEN X2
 WHEN 3 THEN X3
 WHEN 4 THEN X4
 WHEN 5 THEN X5
 WHEN 6 THEN X6
 WHEN 7 THEN X7
 WHEN 8 THEN X8
 WHEN 9 THEN X9
 END AS X
 FROM
 ( SELECT * FROM ( VALUES (2,3,4,5,6,7,8,9) ) XX(X2,X3,X4,X5,X6,X7,X8,X9) ) XXX,
 ( SELECT * FROM ( VALUES (2),(3),(4),(5),(6),(7),(8),(9) ) S(SEQ) ) SS ORDER BY X ) XXXX,

 ( SELECT * FROM ( VALUES (2),(3),(4),(5),(6),(7),(8),(9) ) YY(Y) ) YYY,

 ( SELECT * FROM ( VALUES
 (2,2,4),(2,3,6),(2,4,8),(2,5,10),(2,6,12),(2,7,14),(2,8,16),(2,9,18),
 (3,2,6),(3,3,9),(3,4,12),(3,5,15),(3,6,18),(3,7,21),(3,8,24),(3,9,27),
 (4,2,8),(4,3,12),(4,4,16),(4,5,20),(4,6,24),(4,7,28),(4,8,32),(4,9,36),
 (5,2,10),(5,3,15),(5,4,20),(5,5,25),(5,6,30),(5,7,35),(5,8,40),(5,9,45),
 (6,2,12),(6,3,18),(6,4,24),(6,5,30),(6,6,36),(6,7,42),(6,8,48),(6,9,54),
 (7,2,14),(7,3,21),(7,4,28),(7,5,35),(7,6,42),(7,7,49),(7,8,56),(7,9,63),
 (8,2,16),(8,3,24),(8,4,32),(8,5,40),(8,6,48),(8,7,56),(8,8,64),(8,9,72),
 (9,2,18),(9,3,27),(9,4,36),(9,5,45),(9,6,54),(9,7,63),(9,8,72),(9,9,81)
 ) A(X,Y,XY) ) KUKU

 WHERE KUKU.X =XXXX.X AND KUKU.Y =YYY.Y

上記のSQL文でXXXX、YYY、KUKUの3テーブルでX*Y=XYを表示します。ここでテーブルXXXが行に分解されたXXXXテーブルになっているためWHERE句が"KUKU.X =XXXX.X AND KUKU.Y =YYY.Y"のみでまたKUKUテーブルも1つだけです。
ではどのような仕組みでテーブルXXXの各項目が行に分解されているか見て行きましょう。
ここでも前章のVALUESによるテーブルを利用します。行に分解するためにXXXを分解する個数分のデータを持ったSSテーブルと掛け合わせます。これは上記図1と同じようなものになります。SSの値は同じ値でなければ何でもよく、CASE文の中で処理しますが、普通は１から順番に設定すればいいでしょう。ここではXの値に合わせて2から設定しています。CASE文では行が出現するに従い図1での左上から右下に向かって順番にX2,X3,X4,X5,X6,X7,X8,X9をXとすることにより各項目を行に分解します。SSテーブルと掛け合わせた結果の順番は保証されてないので、ORDER BYでデータの順番を保証します。ここではXでORDER BYしていますが、項目順に並べるのであればCASE文でSSテーブルのSEQ順に項目を処理しているのでSEQ順でORDER BYします。
上記の結果は次の図2ようになります。この例ではX2～X9

1
------
2x2=4
2x3=6
2x4=8
　：
（中略）
　：
9x7=63
9x8=72
9x9=81

　　　　　　　図2 1列の九九表
本来上記のように同じ種類の項目がテーブルにあるような場合は、データの正規化をして別テーブルにすべきですが、既存のシステムで既にこのようなテーブルになっている場合に使用することが出来ます。また、項目が全く異なっていても同じような処理を共通で処理したい場合に使用できます。例えば
mouse_name,mouse_width,mouse_height,rabit_name,rabit_width,rabit_height,cow_name,cow_width,cow_height
というような項目があり、各name毎のwidthとheightの積を計算する時に複数行化しname,width,heightの項目にして、name||'='||width*heightの一つの処理にまとめることができます。因みに複数行化したデータがの項目が複数の場合、CASE句を各項目に対して記述します。

↑

複数行データの1列行化 (DB2,PostgreSQL) †

前章では結果が１行ずつ表示され縦長になり見難くなっています。そこで元のデータのように横並びにしてみましょう。複数行データを1行にする2通りの方法を紹介します。

↑

複数行データの複数項目化 †

複数行のデータを1行にする時に各行のデータを項目として1行データとします。データの個数が決まっている必要があります。MAX関数とGROUP BYとCASE文を使用します。ここでMAX関数はGROUP BYの結果をまとめるために使用しているので、他の関数（MIN）でも可能です。
以下で青字はPostgreSQL用の記述です。

SELECT P1,P2,P3,P4,P5,P6,P7,P8 FROM
( SELECT 
MAX(CASE YYY.Y WHEN 2 THEN XXXX.X||'x'||YYY.Y||'='||KUKU.XY END) as P1,
MAX(CASE YYY.Y WHEN 3 THEN XXXX.X||'x'||YYY.Y||'='||KUKU.XY END) as P2,
MAX(CASE YYY.Y WHEN 4 THEN XXXX.X||'x'||YYY.Y||'='||KUKU.XY END) as P3,
MAX(CASE YYY.Y WHEN 5 THEN XXXX.X||'x'||YYY.Y||'='||KUKU.XY END) as P4,
MAX(CASE YYY.Y WHEN 6 THEN XXXX.X||'x'||YYY.Y||'='||KUKU.XY END) as P5,
MAX(CASE YYY.Y WHEN 7 THEN XXXX.X||'x'||YYY.Y||'='||KUKU.XY END) as P6,
MAX(CASE YYY.Y WHEN 8 THEN XXXX.X||'x'||YYY.Y||'='||KUKU.XY END) as P7,
MAX(CASE YYY.Y WHEN 9 THEN XXXX.X||'x'||YYY.Y||'='||KUKU.XY END) as P8
 FROM
 ( SELECT CASE SEQ
 WHEN 2 THEN X2
 WHEN 3 THEN X3
 WHEN 4 THEN X4
 WHEN 5 THEN X5
 WHEN 6 THEN X6
 WHEN 7 THEN X7
 WHEN 8 THEN X8
 WHEN 9 THEN X9
 END AS X
 FROM
 ( SELECT * FROM ( VALUES (2,3,4,5,6,7,8,9) ) XX(X2,X3,X4,X5,X6,X7,X8,X9) ) XXX,
 ( SELECT * FROM ( VALUES (2),(3),(4),(5),(6),(7),(8),(9) ) S(SEQ) ) SS ORDER BY X ) XXXX,

 ( SELECT * FROM ( VALUES (2),(3),(4),(5),(6),(7),(8),(9) ) YY(Y) ) YYY,

 ( SELECT * FROM ( VALUES
 (2,2,4),(2,3,6),(2,4,8),(2,5,10),(2,6,12),(2,7,14),(2,8,16),(2,9,18),
 (3,2,6),(3,3,9),(3,4,12),(3,5,15),(3,6,18),(3,7,21),(3,8,24),(3,9,27),
 (4,2,8),(4,3,12),(4,4,16),(4,5,20),(4,6,24),(4,7,28),(4,8,32),(4,9,36),
 (5,2,10),(5,3,15),(5,4,20),(5,5,25),(5,6,30),(5,7,35),(5,8,40),(5,9,45),
 (6,2,12),(6,3,18),(6,4,24),(6,5,30),(6,6,36),(6,7,42),(6,8,48),(6,9,54),
 (7,2,14),(7,3,21),(7,4,28),(7,5,35),(7,6,42),(7,7,49),(7,8,56),(7,9,63),
 (8,2,16),(8,3,24),(8,4,32),(8,5,40),(8,6,48),(8,7,56),(8,8,64),(8,9,72),
 (9,2,18),(9,3,27),(9,4,36),(9,5,45),(9,6,54),(9,7,63),(9,8,72),(9,9,81)
 ) A(X,Y,XY) ) KUKU

 WHERE KUKU.X =XXXX.X AND KUKU.Y =YYY.Y
 GROUP BY XXXX.X
 ) ALIAS
ORDER BY p1

P1      P2      P3      P4      P5      P6      P7      P8
------- ------- ------- ------- ------- ------- ------- -------
2x2=4   2x3=6   2x4=8   2x5=10  2x6=12  2x7=14  2x8=16  2x9=18
3x2=6   3x3=9   3x4=12  3x5=15  3x6=18  3x7=21  3x8=24  3x9=27
4x2=8   4x3=12  4x4=16  4x5=20  4x6=24  4x7=28  4x8=32  4x9=36
5x2=10  5x3=15  5x4=20  5x5=25  5x6=30  5x7=35  5x8=40  5x9=45
6x2=12  6x3=18  6x4=24  6x5=30  6x6=36  6x7=42  6x8=48  6x9=54
7x2=14  7x3=21  7x4=28  7x5=35  7x6=42  7x7=49  7x8=56  7x9=63
8x2=16  8x3=24  8x4=32  8x5=40  8x6=48  8x7=56  8x8=64  8x9=72
9x2=18  9x3=27  9x4=36  9x5=45  9x6=54  9x7=63  9x8=72  9x9=81

結果は上記のP1～P8の8項目として表示されます。
複数行データの1行化は通常次のようにメインテーブル(ここではUsers)に対して付加情報テーブル(ここではProperty)を付加情報名＋値の別テーブルとした時にします。ここでは検索条件についても記述しています。WHERE句による検索条件を入れる場合は一旦副照会(ここでは別名W)とします。ここでは集約関数にMAX以外のSUM,MINを使ってみましたが特に意味はありません。

SELECT ID,NAME,AGE,HEIGHT FROM (
SELECT ID,NAME,
SUM(CASE Property_CD WHEN 'age' THEN CAST(VALUE AS INTEGER) END) AS age,
MIN(CASE Property_CD WHEN 'height' THEN VALUE ELSE null END) AS height
FROM
(
SELECT X.ID,X.NAME,Y.Property_CD,Y.VALUE
FROM
(
 SELECT * FROM
     (
     VALUES
 (1,'Arita','Hokkaido'),
 (2,'Kiritani','Aomori'),
 (3,'Sasaki','Nagasaki'),
 (4,'Tanaka','Tokyo'),
 (5,'Nakai','Gunma'),
 (6,'Hurui','Chiba'),
 (7,'Morita','Niigata'),
 (8,'Yamada','Tottori'),
 (9,'Reisen','Kagawa'),
 (10,'Wakasa','Kyoto'),
 (11,'Okumura','Kagoshima')
) Users( id,Name,Prefecture)
) X
,( 
SELECT * FROM
     (
     VALUES 
 (1,'Phone','08012345678'),(1,'age','10'),(1,'height','1600'),(1,'Point','36'),
 (2,'Phone','09045673456'),(2,'age','20'),(2,'height','1822'),(2,'Point','123'),
 (3,'Phone','09087654321'),(3,'age','30'),(3,'height','1767'),(3,'Point','74'),
 (4,'Phone','08043219876'),(4,'age','40'),(4,'height','1538'),(4,'Point','50'),
 (5,'Phone','07045672345'),(5,'age','12'),(5,'height','1672'),(5,'Point','171'),
 (6,'Phone','09054323456'),(6,'age','22'),(6,'height','1783'),(6,'Point','68'),
 (7,'Phone','09098761234'),(7,'age','33'),(7,'height','1525'),(7,'Point','129'),
 (8,'Phone','08065430987'),(8,'age','44'),(8,'height','1792'),(8,'Point','96'),
 (9,'Phone','09076540987'),(9,'age','15'),(9,'height','1679'),(9,'Point','148'),
 (10,'Phone','08034563210'),(10,'age','25'),(10,'height','1614'),(10,'Point','21'),
 (11,'Phone','07078906543'),(11,'age','35'),(11,'height','1557'),(11,'Point','88')
) Property( id,Property_CD,value)

) Y
WHERE X.ID = Y.ID
) Z
GROUP BY ID,NAME
) W
WHERE AGE BETWEEN 20 AND 29
;

データが多くてデータの集計に時間が掛かる場合は、次のようにPropertyデータを取得する時に項目名の条件を入れてPropertyデータの数を絞ります。更に値の範囲の条件があればその条件も入れます。(ここではageに条件を入れています。)値の範囲の条件を入れたデータはnullとなるので最後にIS NOT NULLの条件で省きます。

SELECT ID,NAME,AGE,HEIGHT FROM (
SELECT ID,NAME,
MAX(CASE Property_CD WHEN 'age'    THEN VALUE ELSE null END) AS age,
MIN(CASE Property_CD WHEN 'height' THEN VALUE ELSE null END) AS height
FROM
(
SELECT X.ID,X.NAME,Y.Property_CD,Y.VALUE
FROM
(
 SELECT * FROM
     (
     VALUES
 (1,'Arita','Hokkaido'),
 (2,'Kiritani','Aomori'),
 (3,'Sasaki','Nagasaki'),
 (4,'Tanaka','Tokyo'),
 (5,'Nakai','Gunma'),
 (6,'Hurui','Chiba'),
 (7,'Morita','Niigata'),
 (8,'Yamada','Tottori'),
 (9,'Reisen','Kagawa'),
 (10,'Wakasa','Kyoto'),
 (11,'Okumura','Kagoshima')
) Users( id,Name,Prefecture)
) X
,(
SELECT * FROM
     (
     VALUES 
 (1,'Phone','08012345678'),(1,'age','10'),(1,'height','1600'),(1,'Point','36'),
 (2,'Phone','09045673456'),(2,'age','20'),(2,'height','1822'),(2,'Point','123'),
 (3,'Phone','09087654321'),(3,'age','30'),(3,'height','1767'),(3,'Point','74'),
 (4,'Phone','08043219876'),(4,'age','40'),(4,'height','1538'),(4,'Point','50'),
 (5,'Phone','07045672345'),(5,'age','12'),(5,'height','1672'),(5,'Point','171'),
 (6,'Phone','09054323456'),(6,'age','22'),(6,'height','1783'),(6,'Point','68'),
 (7,'Phone','09098761234'),(7,'age','33'),(7,'height','1525'),(7,'Point','129'),
 (8,'Phone','08065430987'),(8,'age','44'),(8,'height','1792'),(8,'Point','96'),
 (9,'Phone','09076540987'),(9,'age','15'),(9,'height','1679'),(9,'Point','148'),
 (10,'Phone','08034563210'),(10,'age','25'),(10,'height','1614'),(10,'Point','21'),
 (11,'Phone','07078906543'),(11,'age','35'),(11,'height','1557'),(11,'Point','88')
) Property( id,Property_CD,value)

WHERE
 ( Property_CD='age' AND CAST(VALUE AS INTEGER) BETWEEN 20 AND 29)
 OR Property_CD='height'
) Y
WHERE X.ID = Y.ID
) Z
GROUP BY ID,NAME
) W
WHERE AGE IS NOT NULL
;

 id |   name   | age | height
----+----------+-----+--------
 10 | Wakasa   | 25  | 1614
  6 | Hurui    | 22  | 1783
  2 | Kiritani | 20  | 1822

↑

複数行データの1項目化 †

DB2の場合LISTAGG関数を使用して複数行データを1項目にします。
PostgreSQLの場合STRING_AGG関数を使用して複数行データを1項目にします。

--DB2の場合
SELECT LISTAGG( XXXX.X||'x'||YYY.Y||'='||KUKU.XY,'  ' ) WITHIN GROUP(ORDER BY XXXX.X ASC)
--PostgreSQLの場合
SELECT SUBSTR(STRING_AGG( XXXX.X||'x'||YYY.Y||'='||KUKU.XY,'  ' ) ,1,62)
 FROM (
 SELECT CASE SEQ
 WHEN 2 THEN X2
 WHEN 3 THEN X3
 WHEN 4 THEN X4
 WHEN 5 THEN X5
 WHEN 6 THEN X6
 WHEN 7 THEN X7
 WHEN 8 THEN X8
 WHEN 9 THEN X9
 END AS X
 FROM
 ( SELECT * FROM ( VALUES (2,3,4,5,6,7,8,9) ) XX(X2,X3,X4,X5,X6,X7,X8,X9) ) XXX,
 ( SELECT * FROM ( VALUES (2),(3),(4),(5),(6),(7),(8),(9) ) S(SEQ) ) SS ORDER BY X ) XXXX,

 ( SELECT * FROM ( VALUES (2),(3),(4),(5),(6),(7),(8),(9) ) YY(Y) ) YYY,

 ( SELECT * FROM ( VALUES
 (2,2,4),(2,3,6),(2,4,8),(2,5,10),(2,6,12),(2,7,14),(2,8,16),(2,9,18),
 (3,2,6),(3,3,9),(3,4,12),(3,5,15),(3,6,18),(3,7,21),(3,8,24),(3,9,27),
 (4,2,8),(4,3,12),(4,4,16),(4,5,20),(4,6,24),(4,7,28),(4,8,32),(4,9,36),
 (5,2,10),(5,3,15),(5,4,20),(5,5,25),(5,6,30),(5,7,35),(5,8,40),(5,9,45),
 (6,2,12),(6,3,18),(6,4,24),(6,5,30),(6,6,36),(6,7,42),(6,8,48),(6,9,54),
 (7,2,14),(7,3,21),(7,4,28),(7,5,35),(7,6,42),(7,7,49),(7,8,56),(7,9,63),
 (8,2,16),(8,3,24),(8,4,32),(8,5,40),(8,6,48),(8,7,56),(8,8,64),(8,9,72),
 (9,2,18),(9,3,27),(9,4,36),(9,5,45),(9,6,54),(9,7,63),(9,8,72),(9,9,81)
 ) A(X,Y,XY) ) KUKU
 WHERE KUKU.X =XXXX.X AND KUKU.Y =YYY.Y
 GROUP BY XXXX.X
 ORDER BY 1

ここでのGROUP BYはLISTAGG/STRING_AGGで1行化するGROUPを指定します。 LISTAGG/STRING_AGG関数の構文は以下です。

LISTAGG( 文字列,文字列と文字列の間の区切り文字 ) WITHIN GROUP(ORDER BY句 )

STRING_AGG( 文字列,文字列と文字列の間の区切り文字 )

LISTAGG/STRING_AGG関数では、GROUP BYで集約されるデータすべてが連結されるので、1行化するデータ数が不確定の場合使用すると便利です。LISTAGG/STRING_AGG関数で連結可能な最大文字列長には制限があるので、各DBの資料で確認してください。 LISTAGG/STRING_AGG関数はMySQL では group_concat()、Oracle Database では listagg() と名前が異なります。

1
--------------------------------------------------------------
2x2=4  2x3=6  2x4=8  2x5=10  2x6=12  2x7=14  2x8=16  2x9=18
3x2=6  3x3=9  3x4=12  3x5=15  3x6=18  3x7=21  3x8=24  3x9=27
4x2=8  4x3=12  4x4=16  4x5=20  4x6=24  4x7=28  4x8=32  4x9=36
5x2=10  5x3=15  5x4=20  5x5=25  5x6=30  5x7=35  5x8=40  5x9=45
6x2=12  6x3=18  6x4=24  6x5=30  6x6=36  6x7=42  6x8=48  6x9=54
7x2=14  7x3=21  7x4=28  7x5=35  7x6=42  7x7=49  7x8=56  7x9=63
8x2=16  8x3=24  8x4=32  8x5=40  8x6=48  8x7=56  8x8=64  8x9=72
9x2=18  9x3=27  9x4=36  9x5=45  9x6=54  9x7=63  9x8=72  9x9=81

結果は上記1の1項目だけとなります。

↑

区切り文字によるデータの分割 : (DB2) †

以下は項目DATAの内容を区切り文字'/'で分割して複数行にする方法です。ここでもデータを分割するためにVALUES(SEQTBLを作成)を使用しています。最大何分割必要か予めデータの内容を調べておきます。分割に必要な数だけCASE文とVALUESを準備します。
データの分割にはINSTR関数で区切り文字の位置を求めSUBSTR関数で文字列を切り取ります。INST関数の4番目の引数で何番目に出現した区切り文字かを指定します。SEQの値が増えるに従って、この値も順番に増やすだけです。
LENGTH(DATA) - LENGTH( REPLACE(DATA,'/','') )で区切り文字の数を計算しています。分割が必要ない時と、分割したデータの最後のデータでINSTR関数の結果が0となりSUBSTRでエラーになるため区切り文字の数を求めてその対応をしています。
DENSE_RANK関数とSEQでデータ表示の順番を制御しています。
全角のデータを分割する場合、区切り文字の値が全角文字の片割れにならないように注意して下さい。

SELECT NMBR,SEQ,NAME,DESCRIPTION FROM (
    SELECT DENSE_RANK() OVER(ORDER BY NAME) AS NMBR,SEQ,NAME,
        CASE
            WHEN SEQ=0 THEN 
                CASE WHEN 0=NUM THEN
                    DATA
                ELSE
                    SUBSTR(DATA,1,INSTR(DATA,'/',1,1)-1)
                END
            WHEN SEQ=1 AND 0 < NUM THEN
                CASE  WHEN 1=NUM THEN
                    SUBSTR(DATA,INSTR(DATA,'/',1,1)+1)
                ELSE
                    SUBSTR(DATA,INSTR(DATA,'/',1,1)+1,INSTR(DATA,'/',1,2)-INSTR(DATA,'/',1,1)-1 )
                END
            WHEN SEQ=2 AND 1 < NUM THEN
                CASE  WHEN 2=NUM THEN
                    SUBSTR(DATA,INSTR(DATA,'/',1,2)+1)
                ELSE
                    SUBSTR(DATA,INSTR(DATA,'/',1,2)+1,INSTR(DATA,'/',1,3)-INSTR(DATA,'/',1,2)-1 )
                END
            WHEN SEQ=3 AND 2 < NUM THEN
                CASE  WHEN 3=NUM THEN
                    SUBSTR(DATA,INSTR(DATA,'/',1,3)+1)
                ELSE
                    SUBSTR(DATA,INSTR(DATA,'/',1,3)+1,INSTR(DATA,'/',1,4)-INSTR(DATA,'/',1,3)-1 )
                END
            ELSE NULL
        END AS DESCRIPTION
    FROM
        (SELECT NAME,DATA, (LENGTH(DATA) - LENGTH( REPLACE(DATA,'/','') )) AS NUM
            FROM (
               VALUES
                    ('NAME1','11AAAAA/12BBBBB/13CCCCC')
                   ,('NAME2','21AAAAA/22BBBBB')
                   ,('NAME3','31AAAAA/32BBBBB/33CCCCC')
                   ,('NAME4','41AAAAA/42BBBBB/43CCCCC/44DDDDD')
                   ,('NAME5','51AAAAA/52BBBBB/53CCCCC')
                   ,('NAME6','61AAAAA/62BBBBB/63CCCCC/64DDDDD')
                   ,('NAME7','71AAAAA')
                    ) VIRTUAL(NAME,DATA)
        ) V,
        (SELECT SEQ FROM ( VALUES (0),(1),(2),(3)) SEQTBL(SEQ)) S
    WHERE S.SEQ <=V.NUM
)
ORDER BY NMBR,SEQ
;

上記をもう少し工夫して分割数が増えて修正するところをVALUESによるSEQTBLのデータを増やすだけでいいようにしました。

SELECT NMBR,SEQ,NAME,DESCRIPTION FROM (
    SELECT DENSE_RANK() OVER(ORDER BY NAME) AS NMBR,SEQ,NAME,
        CASE
            WHEN SEQ=0 THEN
                CASE WHEN 0=NUM THEN
                    DATA
                ELSE
                    SUBSTR(DATA,1,INSTR(DATA,'/',1,1)-1)
                END
            WHEN SEQ=NUM THEN
                SUBSTR(DATA,INSTR(DATA,'/',1,SEQ)+1)
            ELSE
                SUBSTR(DATA,INSTR(DATA,'/',1,SEQ)+1,INSTR(DATA,'/',1,SEQ+1)-INSTR(DATA,'/',1,SEQ)-1 )
        END AS DESCRIPTION
    FROM
        (SELECT NAME,DATA, (LENGTH(DATA) - LENGTH( REPLACE(DATA,'/','') )) AS NUM
            FROM (
               VALUES
                    ('NAME1','11AAAAA/12BBBBB/13CCCCC')
                   ,('NAME2','21AAAAA/22BBBBB')
                   ,('NAME3','31AAAAA/32BBBBB/33CCCCC')
                   ,('NAME4','41AAAAA/42BBBBB/43CCCCC/44DDDDD')
                   ,('NAME5','51AAAAA/52BBBBB/53CCCCC')
                   ,('NAME6','61AAAAA/62BBBBB/63CCCCC/64DDDDD')
                   ,('NAME7','71AAAAA')
                    ) VIRTUAL(NAME,DATA)
        ) V,
        (SELECT SEQ FROM ( VALUES (0),(1),(2),(3)) SEQTBL(SEQ)) S
    WHERE S.SEQ <=V.NUM
)
ORDER BY NMBR,SEQ
;

結果は以下のようになります。

NMBR  SEQ  NAME  DESCRIPTION
----- ---- ----- -----------
    1    0 NAME1 11AAAAA
    1    1 NAME1 12BBBBB
    1    2 NAME1 13CCCCC
    2    0 NAME2 21AAAAA
    2    1 NAME2 22BBBBB
    3    0 NAME3 31AAAAA
    3    1 NAME3 32BBBBB
    3    2 NAME3 33CCCCC
    4    0 NAME4 41AAAAA
    4    1 NAME4 42BBBBB
    4    2 NAME4 43CCCCC
    4    3 NAME4 44DDDDD
    5    0 NAME5 51AAAAA
    5    1 NAME5 52BBBBB
    5    2 NAME5 53CCCCC
    6    0 NAME6 61AAAAA
    6    1 NAME6 62BBBBB
    6    2 NAME6 63CCCCC
    6    3 NAME6 64DDDDD
    7    0 NAME7 71AAAAA

↑

区切り文字によるデータの分割 : (PostgreSQL) †

PostgreSQLの場合、INSTRのように検索文字列の出現回数を指定してその位置を返す関数がありません。PostgreSQLのマニュアルの「Porting from Oracle PL/SQL」の章にこれに相当するINSTR関数のCREATE文が記述されています。以下はそのマニュアルからの抜粋です。この記述を上記SQLを使用する前に記述します。

CREATE FUNCTION instr(string varchar, string_to_search varchar,
                      beg_index integer, occur_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    occur_number integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    i integer;
    length integer;
    ss_length integer;
BEGIN
    IF beg_index > 0 THEN
        beg := beg_index;
        temp_str := substring(string FROM beg_index);

        FOR i IN 1..occur_index LOOP
            pos := position(string_to_search IN temp_str);

            IF i = 1 THEN
                beg := beg + pos - 1;
            ELSE
                beg := beg + pos;
            END IF;

            temp_str := substring(string FROM beg + 1);
        END LOOP;

        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN beg;
        END IF;
    ELSE
        ss_length := char_length(string_to_search);
        length := char_length(string);
        beg := length + beg_index - ss_length + 2;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            pos := position(string_to_search IN temp_str);

            IF pos > 0 THEN
                occur_number := occur_number + 1;

                IF occur_number = occur_index THEN
                    RETURN beg;
                END IF;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

使用後は以下でDROPしておきましょう。

DROP FUNCTION INSTR(varchar, varchar, integer, integer);

↑

データの整形 : (DB2) †

↑

0詰め †

桁数が決まっている場合(この例では2桁)の0詰め "2" -> "02"

COALESCE(RIGHT('0'||TRIM(CHAR(data1)),2),'00')

↑

0付け †

データタイプがDECIMALなどで小数点以下の場合".12345"のように左端が"."の場合"0"を追加し"0.12345"にする。

CASE WHEN SUBSTR(TRIM(COALESCE(CHAR(data1),'')),1,1)='.' THEN '0'||TRIM(CHAR(data1)) ELSE TRIM(COALESCE(CHAR(data1),'')) END

Last-modified: 2016-06-14 (火) 12:38:30 (2874d)