Split comma-separated values and map them to original ID in SQLite

Question

I have a table called articles that contains data in the following format:

id|categories
--+----------
1|123,13,43
2|1,3,15
3|9,17,44,18,3

For testing purposes, you may create this table using the following SQL commands:

CREATE TABLE articles(id INTEGER PRIMARY KEY, categories TEXT);
INSERT INTO articles VALUES(1, '123,13,43'), (2, '1,3,15'), (3, '9,17,44,18,3');

Now I would like to split the values of the categories column so that a table like the following one is the result:

id|category
--+--------
1|123
1|13
1|43
2|1
2|3
2|15
3|9
3|17
3|44
3|18
3|3

As you can see, I would like to bring the original table into the First normal form.

I already know how to split just one row in this way, from this answer. The following code example just takes the second row (i.e. the one where id=2) and splits them in the desired way:

WITH split(article_id, word, str, offsep) AS
(
    VALUES
    (
        2,
        '',
        (SELECT categories FROM articles WHERE id=2),
        1
    )
    UNION ALL
    SELECT
        article_id,
        substr(str, 0, CASE WHEN instr(str, ',') THEN instr(str, ',') ELSE length(str)+1 END),
        ltrim(substr(str, instr(str, ',')), ','),
        instr(str, ',')
        FROM split
        WHERE offsep
) SELECT article_id, word FROM split WHERE word!='';

Of course this is very unflexible, as the article ID needs to be hard-coded. So, now my question is: What do I have to add to or change in the upper SQLite code to make it operate on all rows and output the desired result?

Is it really have to be done in SQL (maybe 'yes', just verifying)? Can you do that on bash? — David דודו Markovitz, Oct 27 '16 at 16:09
@DuduMarkovitz I don't think it would be very nice using bash... And I do this for fun/educational purposes anyway, so learning about an SQL way would be better. — sigalor, Oct 27 '16 at 17:09
One liner awk would do the trick. There are many similar questions in this site. SQLite is not natural for this job. — David דודו Markovitz, Oct 27 '16 at 17:10

score 3 · Accepted Answer · answered Oct 29 '16 at 15:08

After some playing around, I finally figured out the solution myself. It also takes care of rows that have '' or NULL as values for categories:

-- create temporary table which buffers the maximum article ID, because SELECT MAX can take a very long time on huge databases
DROP TABLE IF EXISTS max_article_id;
CREATE TEMP TABLE max_article_id(num INTEGER);
INSERT INTO max_article_id VALUES((SELECT MAX(id) FROM articles));

WITH RECURSIVE split(article_id, word, str, offsep) AS
(
    VALUES ( 0, '', '', 0 )                                      -- begin with dummy article 0 (which does not actually exist) to avoid code duplication
    UNION ALL
    SELECT
        CASE WHEN offsep==0 OR str IS NULL
            THEN article_id+1                                    -- go to next article if the current one is finished
            ELSE article_id                                      -- and keep the current one in the opposite case
        END,
        CASE WHEN offsep==0 OR str IS NULL
            THEN ''
            ELSE substr(str, 0, CASE WHEN instr(str, ',') THEN instr(str, ',') ELSE length(str)+1 END)
        END,
        CASE WHEN offsep==0 OR str IS NULL                       -- when str==NULL, then there has been a NULL value for the categories cell of the current article
            THEN (SELECT categories FROM articles WHERE id=article_id+1)
            ELSE ltrim(substr(str, instr(str, ',')), ',')
        END,
        CASE WHEN offsep==0 OR str IS NULL                       -- offsep==0 means that the splitting was finished in the previous iteration
            THEN 1                                               -- offsep==1 means that splitting the categories for a new article will begin in the next iteration
            ELSE instr(str, ',')                                 -- the actual string splitting stuff is explained and taken from here: http://stackoverflow.com/a/32051164
        END
        FROM split
        WHERE article_id<=(SELECT * FROM max_article_id)         -- stop getting new articles when the maximum article ID is reached
) SELECT article_id, word AS category FROM split WHERE word!=''; -- only select article_id and word from the result to use output the desired table layout

Thanks for this, worked very well, except sqlite seems to start indexes at 1 so I changed this line in setting the word: `ELSE substr(str, 1, CASE WHEN instr(str, ',') THEN instr(str, ',') ELSE length(str)+1 END)` — Eric G, Oct 06 '17 at 20:23

score 3 · Answer 2 · answered Feb 27 '19 at 02:59

Hi this might be a few years late, but I have a simpler solution using my revised answer in How to split comma-separated value in SQLite?

CREATE TABLE articles(id INTEGER PRIMARY KEY, categories TEXT);
INSERT INTO articles VALUES(1, '123,13,43'), (2, '1,3,15'),
(3, '9,17,44,18,3'), (4, ''), (5, NULL);

WITH split(id, category, str) AS (
    SELECT id, '', categories||',' FROM articles
    UNION ALL SELECT id,
    substr(str, 0, instr(str, ',')),
    substr(str, instr(str, ',')+1)
    FROM split WHERE str
) SELECT id, category FROM split WHERE category ORDER BY id;

Output is as you expected:

id|category
1|123
1|13
1|43
2|1
2|3
2|15
3|9
3|17
3|44
3|18
3|3

Split comma-separated values and map them to original ID in SQLite

2 Answers2

Linked