SQLite: Comma Separated Values To New Table & Columns

user_442743 0
Viewed 334

I have a SQLite table:

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

I want to separate this directory structure into a new Table with new Columns. New table:

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

What's the best approach? As a start, I have separated the strings by character "/" but currently this only makes new rows, not columns:

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!='' ;
csv directory sqlite database split
1 Answers

First create the new table with as many path columns as you need:

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

and then use conditional aggregation to insert rows to the table:

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

I modified the split cte to include the column idx which is used for the order of the path columns.

See the demo.
Results:

|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
user_104988280
Related