SQLite 3.27

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

CREATE TABLE listfile (FileDataID INTEGER, Path TEXT);
✓
INSERT INTO listfile (FileDataID, Path) VALUES
  ('1', 'video/gopro/father/mov001.mp4'),
  ('2', 'videos/gopro/father/mov001.mp4'),
  ('3', 'picture/family/father/Oldman.jpg'),
  ('4', 'pictures/family/father/Oldman.jpg'),
  ('5', 'documents/legal/father/estate/will.doc'),
  ('6', 'documents/legal/father/estate/will.doc'),
  ('7', 'documents/legal/father/estate/newyork/albany/will.doc'),
  ('8', 'video/gopro/father/newyork/albany/holiday/christmas/2002/mov001.mp4'),
  ('9', 'pictures/family/father/newyork/albany/holiday/christmas/2002/july/Oldman.jpg'),
  ('10', 'pictures/family/father/newyork/albany/holiday/christmas/2002/jul/Oldman.jpg');
✓
CREATE TABLE tablename(FileDataID INTEGER PRIMARY KEY, path0 TEXT, path1 TEXT, path2 TEXT, path3 TEXT, path4 TEXT, path5 TEXT, path6 TEXT, path7 TEXT, path8 TEXT, path9 TEXT);
✓
WITH split AS (
  SELECT 0 idx,
         FileDataID,
         SUBSTR(SUBSTR(Path, 1), 1, INSTR(SUBSTR(Path, 1) || '/', '/') - 1) item,
         SUBSTR(SUBSTR(Path, 1), INSTR(SUBSTR(Path, 1) || '/', '/') + 1) value
  FROM listfile
  UNION ALL
  SELECT idx + 1,
         FileDataID,
         SUBSTR(value, 1, INSTR(value || '/', '/') - 1),
         SUBSTR(value, INSTR(value || '/', '/') + 1)
  FROM split
  WHERE LENGTH(value) > 0
)
INSERT INTO tablename 
SELECT FileDataID,
       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),
       MAX(CASE WHEN idx = 5 THEN item END),
       MAX(CASE WHEN idx = 6 THEN item END),
       MAX(CASE WHEN idx = 7 THEN item END),
       MAX(CASE WHEN idx = 8 THEN item END),
       MAX(CASE WHEN idx = 9 THEN item END)
FROM split
GROUP BY FileDataID
✓
SELECT * FROM tablename


FileDataID
path0
path1
path2
path3
path4
path5
path6
path7
path8
path9


1
video
gopro
father
mov001.mp4
null
null
null
null
null
null

2
videos
gopro
father
mov001.mp4
null
null
null
null
null
null

3
picture
family
father
Oldman.jpg
null
null
null
null
null
null

4
pictures
family
father
Oldman.jpg
null
null
null
null
null
null

5
documents
legal
father
estate
will.doc
null
null
null
null
null

6
documents
legal
father
estate
will.doc
null
null
null
null
null

7
documents
legal
father
estate
newyork
albany
will.doc
null
null
null

8
video
gopro
father
newyork
albany
holiday
christmas
2002
mov001.mp4
null

9
pictures
family
father
newyork
albany
holiday
christmas
2002
july
Oldman.jpg

10
pictures
family
father
newyork
albany
holiday
christmas
2002
jul
Oldman.jpg

FileDataID	path0	path1	path2	path3	path4	path5	path6	path7	path8	path9
1	video	gopro	father	mov001.mp4	null	null	null	null	null	null
2	videos	gopro	father	mov001.mp4	null	null	null	null	null	null
3	picture	family	father	Oldman.jpg	null	null	null	null	null	null
4	pictures	family	father	Oldman.jpg	null	null	null	null	null	null
5	documents	legal	father	estate	will.doc	null	null	null	null	null
6	documents	legal	father	estate	will.doc	null	null	null	null	null
7	documents	legal	father	estate	newyork	albany	will.doc	null	null	null
8	video	gopro	father	newyork	albany	holiday	christmas	2002	mov001.mp4	null
9	pictures	family	father	newyork	albany	holiday	christmas	2002	july	Oldman.jpg
10	pictures	family	father	newyork	albany	holiday	christmas	2002	jul	Oldman.jpg