SQLite：逗号分隔值到新表和列

Question

我有一个SQLite表：

fileId | path
1        /video/gopro/father/mov001.mp4
2        /pictures/family/father/Oldman.jpg
3        /documents/legal/father/estate/will.doc

我想把这个目录结构分成一个新的表和新的列。新表格：

fileId | path0   | path1 | path 2 | path3      | path4  .... (Define an upper limit of sub-dirs) 
1        video     gopro   father   mov001.mp4
2        pictures  family  father   Oldman.jpg
3        documents legal   father   estate       will.doc

最好的方法是什么？首先，我用字符"分隔字符串/"但目前这只生成新行，而不是列：

WITH split(Path, str) AS (
SELECT '', Path||'/' FROM listfile
UNION ALL SELECT
substr(str, 0, instr(str, '/')),
substr(str, instr(str, '/')+1)
FROM split WHERE str !=''
) SELECT Path FROM split WHERE Path!='' ;

Answer 1

首先创建一个具有所需path列数的新表：

CREATE TABLE tablename(
fileId INTEGER PRIMARY KEY, 
path0 TEXT, 
path1 TEXT, 
path2 TEXT, 
path3 TEXT, 
path4 TEXT
);

然后使用条件聚合将行插入到表中：

WITH split AS (
SELECT 0 idx,
fileId,
SUBSTR(SUBSTR(Path, 2), 1, INSTR(SUBSTR(Path, 2) || '/', '/') - 1) item,
SUBSTR(SUBSTR(Path, 2), INSTR(SUBSTR(Path, 2) || '/', '/') + 1) value
FROM listfile
UNION ALL
SELECT idx + 1,
fileId,
SUBSTR(value, 1, INSTR(value || '/', '/') - 1),
SUBSTR(value, INSTR(value || '/', '/') + 1)
FROM split
WHERE LENGTH(value) > 0
)
INSERT INTO tablename 
SELECT fileId,
MAX(CASE WHEN idx = 0 THEN item END),
MAX(CASE WHEN idx = 1 THEN item END),
MAX(CASE WHEN idx = 2 THEN item END),
MAX(CASE WHEN idx = 3 THEN item END),
MAX(CASE WHEN idx = 4 THEN item END)
FROM split
GROUP BY fileId

我修改了splitcte以包括用于path列的顺序的列idx

请参阅演示
结果：

|fileId | path0     | path1  | path2  | path3      | path4   
|-----: | :-------- | :----- | :----- | :--------- | :-------
|     1 | video     | gopro  | father | mov001.mp4 | null    
|     2 | pictures  | family | father | Oldman.jpg | null    
|     3 | documents | legal  | father | estate     | will.doc