sqlite3でsplit

sqliteって関数少ないですよね...

やりたいこと

• こんなCSVなフィールドデータを...

key	csv
key1	this,is,a,pen
key2	hello,world
key3	I,am,a,boy

• こんな風に整形したいんです。

key	value
key1	this
key1	is
key1	a
key1	pen
key2	hello
key2	world
key3	I
key3	am
key3	a
key3	boy

SQL

WITH RECURSIVEってのがミソですね。

-- テーブル定義
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;

実行結果

できました！

sqlite> select * from KV;
key         val
----------  -------------
key1        this,is,a,pen
key2        hello,world
key3        I,am,a,boy
sqlite>
sqlite> 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;
KEY         fld
----------  ----------
key1        this
key1        is
key1        a
key1        pen
key2        hello
key2        world
key3        I
key3        am
key3        a
key3        boy
sqlite>
sqlite>