CREATE TABLE ORION
(
NAME VARCHAR(100) ,
COST VARCHAR(1000)
) IN USERSPACE1;
新增測試資料
Insert into ORION (NAME, COST) Values ('ABC', '101');
Insert into ORION (NAME, COST) Values ('XYZ', '100,101,102,204,203');
Insert into ORION (NAME, COST) Values ('HJK', '130,206');
Insert into ORION (NAME, COST) Values ('KLM', '');
Insert into ORION (NAME, COST) Values ('OPU', '105,109,113,118,117,199,288,287');
運用Recursive SQL將COST欄位裡用逗號區分的資料拆解成多筆資料
WITH N(COL,NAME,COST,COST1) AS (
SELECT 1 COL,NAME,','||COST||',',','||COST||','
FROM ORION
WHERE COST > ''
UNION ALL
SELECT N.COL + 1,N.NAME,N.COST,
SUBSTR(N.COST1,LOCATE(',',N.COST1,2))
FROM N
WHERE N.COL + 1 <= LENGTH(N.COST) -
LENGTH(REPLACE(N.COST,',','')) - 1
)
SELECT NAME,SUBSTR(COST1,2,LOCATE(',',COST1,2) - 2)
FROM N
UNION
SELECT NAME,COST
FROM ORION WHERE COST = ''
ORDER BY NAME;
得到以下結果
NAME COST
------- -------
ABC 101
HJK 130
HJK 206
KLM
OPU 105
OPU 109
OPU 113
OPU 117
OPU 118
OPU 199
OPU 287
OPU 288
XYZ 100
XYZ 101
XYZ 102
XYZ 203
XYZ 204
