SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE listfile (fileId INTEGER, path TEXT);
✓
INSERT INTO listfile (fileId, path) VALUES
  ('1', '/video/gopro/father/mov001.mp4'),
  ('2', '/pictures/family/father/Oldman.jpg'),
  ('3', '/documents/legal/father/estate/will.doc');
✓
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
✓
SELECT * FROM tablename


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

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