title: Split values in parts with sqlite
body: <p>I'm struggling to convert</p>
  
  <pre class="lang-none prettyprint-override"><code>a | a1,a2,a3
  b | b1,b3
  c | c2,c1
  </code></pre>
  
  <p>to:</p>
  
  <pre class="lang-none prettyprint-override"><code>a | a1
  a | a2
  a | a3
  b | b1
  b | b2
  c | c2
  c | c1
  </code></pre>
  
  <p>Here are data in sql format:</p>
  
  <pre class="lang-sql prettyprint-override"><code>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');
  </code></pre>
  
  <p>The solution is probably recursive Common Table Expression.</p>
  
  <p><br><br></p>
  
  <hr>
  
  <p>Here's an example which does something similar to a single row:</p>
  
  <pre class="lang-sql prettyprint-override"><code>WITH RECURSIVE list( element, remainder ) AS (
      SELECT NULL AS element, '1,2,3,4,5' AS remainder
          UNION ALL
      SELECT
          CASE
              WHEN INSTR( remainder, ',' )&gt;0 THEN
                  SUBSTR( remainder, 0, INSTR( remainder, ',' ) )
              ELSE
                  remainder
          END AS element,
          CASE
              WHEN INSTR( remainder, ',' )&gt;0 THEN
                  SUBSTR( remainder, INSTR( remainder, ',' )+1 )
              ELSE
                  NULL
          END AS remainder
      FROM list
      WHERE remainder IS NOT NULL
  )
  SELECT * FROM list;
  </code></pre>
  
  <p>(originally from this blog post: <a href="https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial" rel="noreferrer">https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial</a>)</p>
  
  <p>It produces:</p>
  
  <pre><code>element | remainder
  -------------------
  NULL    | 1,2,3,4,5
  1       | 2,3,4,5
  2       | 3,4,5
  3       | 4,5
  4       | 5
  5       | NULL
  </code></pre>
  
  <p>the problem is thus to apply this to each row in a table.</p>