Split values in parts with sqlite

Question

I'm struggling to convert

a | a1,a2,a3
b | b1,b3
c | c2,c1

to:

a | a1
a | a2
a | a3
b | b1
b | b2
c | c2
c | c1

Here are data in sql format:

CREATE TABLE data(
  "one"  TEXT,
  "many" TEXT
);
INSERT INTO "data" VALUES('a','a1,a2,a3');
INSERT INTO "data" VALUES('b','b1,b3');
INSERT INTO "data" VALUES('c','c2,c1');

The solution is probably recursive Common Table Expression.

Here's an example which does something similar to a single row:

WITH RECURSIVE list( element, remainder ) AS (
    SELECT NULL AS element, '1,2,3,4,5' AS remainder
        UNION ALL
    SELECT
        CASE
            WHEN INSTR( remainder, ',' )>0 THEN
                SUBSTR( remainder, 0, INSTR( remainder, ',' ) )
            ELSE
                remainder
        END AS element,
        CASE
            WHEN INSTR( remainder, ',' )>0 THEN
                SUBSTR( remainder, INSTR( remainder, ',' )+1 )
            ELSE
                NULL
        END AS remainder
    FROM list
    WHERE remainder IS NOT NULL
)
SELECT * FROM list;

(originally from this blog post: https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial)

It produces:

element | remainder
-------------------
NULL    | 1,2,3,4,5
1       | 2,3,4,5
2       | 3,4,5
3       | 4,5
4       | 5
5       | NULL

the problem is thus to apply this to each row in a table.

score 5 · Accepted Answer · answered Sep 29 '17 at 08:04

5

Yes, a recursive common table expression is the solution:

with x(one, firstone, rest) as 
(select one, substr(many, 1, instr(many, ',')-1) as firstone, substr(many, instr(many, ',')+1) as rest from data where many like "%,%"
   UNION ALL
 select one, substr(rest, 1, instr(rest, ',')-1) as firstone, substr(rest, instr(rest, ',')+1) as rest from x    where rest like "%,%" LIMIT 200
)
select one, firstone from x UNION ALL select one, rest from x where rest not like "%,%" 
ORDER by one;

Output:

a|a1
a|a2
a|a3
b|b1
b|b3
c|c2
c|c1

answered Sep 29 '17 at 08:04

Yunnosch

26,130
9
42
54

1

Thank you. Why `limit 200`? – Adobe Sep 29 '17 at 09:37
1

When doing recursive things, taking an extra precaution to ensure not ending in some kind of endless loop (which only gurus reliably avoid - and I am none) is recommended. Obviously once it works as desired, this can be removed. However it is not so expensive as to make that really necessary. If the number of expected lines is larger increase the value, or remove it completely once you are absolutely confident. – Yunnosch Sep 29 '17 at 17:52

score 2 · Answer 2 · answered Feb 06 '20 at 10:28

Check my answer in How to split comma-separated value in SQLite?. This will give you the transformation in a single query rather than having to apply to each row.

-- using your data table assuming that b3 is suppose to be b2

WITH split(one, many, str) AS (
    SELECT one, '', many||',' FROM data
    UNION ALL SELECT one,
    substr(str, 0, instr(str, ',')),
    substr(str, instr(str, ',')+1)
    FROM split WHERE str !=''
) SELECT one, many FROM split WHERE many!='' ORDER BY one;

a|a1
a|a2
a|a3
b|b1
b|b2
c|c2
c|c1

Split values in parts with sqlite

2 Answers2

Linked

Related