SQLite Forum

This demonstrates a way to split and flatten separated values in a column.  It uses one column of the input as a key to identify which row the separated values belong to and lists it accordingly.   
Not sure if it is exactly what you needed, but say if not, it should be easy to adapt the format.

```
  -- SQLite version 3.35.4  [ Release: 2021-04-02 ]  on  SQLitespeed version 2.1.3.11.
================================================================================================

-- Make temporary Space-Separated-Value table

DROP TABLE IF EXISTS tmpssv;
CREATE TABLE tmpssv (     
  ID INTEGER PRIMARY KEY,
  keyCol TEXT,
  ssvCol TEXT
);


-- And fill it with some space-separated content

INSERT INTO tmpssv (ID, keyCol, ssvCol) VALUES
    (1, 'foo', '4 66 51 3009 2 678')
  , (2, 'bar', 'Sputnik Discovery')
  , (3, 'baz', '101 I-95 104')
  , (4, 'foz', 'Amsterdam  Beijing  London  Moscow  New York  Paris  Tokyo')
  , (5, 'one', 'John')
;


-- The CTE Query that takes a column (string) and iteratively splits off the first value (up to the first space) until no more text is left in the string.

WITH ssvrec(i, l, c, r) AS (
      SELECT keyCol, 1,
             ssvCol||' ', '' -- Forcing a space at the end of ssvCol removes complicated checking later
        FROM tmpssv
       WHERE 1
    UNION ALL
      SELECT i,
             instr( c, ' ' ) AS vLength,
             substr( c, instr( c, ' ' ) + 1) AS vRemainder,
             trim( substr( c, 1, instr( c, ' ' ) - 1) ) AS vSSV
        FROM ssvrec
       WHERE vLength > 0
    )
  SELECT ID, keyCol, r AS ssvValues
    FROM tmpssv, ssvrec
  WHERE keyCol = i AND r <> ''
  ORDER BY ID, keyCol
;

-- I've used compact notation to avoid long wordy SQL, but the short column names basically represent:
--   i = Key of unpacked row
--   l = Length of text remaining to be unpacked
--   c = Column text remaining to be unpacked
--   r = Resulting current unpacked (split-off) ssv value


-- The Resulting split/unpacked values:

  --  ID|keyCol|ssvValues  
  -- ---|------|-----------
  --  1 |  foo |2          
  --  1 |  foo |3009       
  --  1 |  foo |4          
  --  1 |  foo |51         
  --  1 |  foo |66         
  --  1 |  foo |678        
  --  2 |  bar |Discovery  
  --  2 |  bar |Sputnik    
  --  3 |  baz |101        
  --  3 |  baz |104        
  --  3 |  baz |I-95       
  --  4 |  foz |Amsterdam  
  --  4 |  foz |Beijing    
  --  4 |  foz |London     
  --  4 |  foz |Moscow     
  --  4 |  foz |New        
  --  4 |  foz |Paris      
  --  4 |  foz |Tokyo      
  --  4 |  foz |York       
  --  5 |  one |John       


-- Cleanup

DROP TABLE IF EXISTS tmpssv;

```