SQL †SQL文のサンプルです。 SQL内データ : VALUES (DB2,PostgreSQL) †VALUESを使用することによりSQL文内でデータを持つことができます。この利点はDBのテーブルにアクセスしなくてもSQLを実行できることと、ちょっとしたテーブルをDBにテーブルを作成しなくてもSQL文内(メモリー内)にテーブルを作成して利用できることです。 (1)構文 †VALUESを使用する場合の構文は以下のようになります。テーブル名または項目名はSQL文の中で使用しない場合不要です。テーブル名とはVALUESで設定したデータをテーブルとして扱う時の名前になります。(データ,データ...)が1行のデータで複数の項目を表します。(),()...が複数の列のデータを表します。 ( VALUES (データ,データ...),(データ,データ...)...) テーブル名(項目名1,項目名2...) (2)SQLで計算 †例えば日付だけをSQLで計算したい場合以下のように何らかのテーブル(ここではSYSIBM.SYSDUMMY1でデータは1件)を使用して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 九九組合わせ表 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文になります。 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つだけです。 1 ------ 2x2=4 2x3=6 2x4=8 : (中略) : 9x7=63 9x8=72 9x9=81 図2 1列の九九表 複数行データの1列行化 (DB2,PostgreSQL) †前章では結果が1行ずつ表示され縦長になり見難くなっています。 そこで元のデータのように横並びにしてみましょう。複数行データを1行にする2通りの方法を紹介します。 複数行データの複数項目化 †複数行のデータを1行にする時に各行のデータを項目として1行データとします。データの個数が決まっている必要があります。MAX関数とGROUP BYとCASE文を使用します。ここでMAX関数はGROUP BYの結果をまとめるために使用しているので、他の関数(MIN)でも可能です。 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項目として表示されます。 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項目にします。 --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を準備します。 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 (3094d)
|