■

• • テーブル定義

CREATE TABLE KV(key TEXT,val TEXT);

• • データ設定

INSERT INTO KV(key,val) VALUES
("key1","this,is,a,pen"),
("key2","hello,world"),
("key3","I,am,a,boy");

• • クエリー

WITH RECURSIVE split(KEY,idx,fld,remain) AS
(SELECT KEY,
instr(val,',') AS idx,
substr(val,1,instr(val,',')-1) AS fld,
substr(val, instr(val,',')+1)||',' AS remain
FROM KV
UNION ALL SELECT KEY,
instr(remain,',') AS idx,
substr(remain,1,instr(remain,',')-1) AS fld,
substr(remain, instr(remain,',')+1) AS remain
FROM split
WHERE remain != '' )
SELECT KEY,
fld
FROM split
ORDER BY KEY ASC;