Extract value from table in new column with SQL query

I have a table with a text column in the database where the records for each row are recorded as lists e.g. column test:

[['a','1'],['b','2'],['c','3']]
[['a','2'],['a','4'],['c','8'],['d','3']
[['b','3'],['c',5'],['c','6'],['d','4']]

I want to extract the values into three new columns x y z where

• x contains all the values of a and b;
• y contains all the values of c;
• z contains all the values of d e.g.

Like this:

     x                         y                    z
  ['a','1'],['b','2']    ['c','3']
  ['a','2'],['a','4']    ['c','8']                ['d','3']
  ['b','3']              ['c','5'],['c','6']      ['d','4']

Is it possible to do this with a SQL statement?

Thanks in advance

Answer

You need a recursive CTE to split each row and then GROUP_CONCAT() to collect the values according to your requirement:

WITH cte AS (
  SELECT rowid,
         SUBSTR(test, 1, INSTR(test || ';', ';') - 1) col,
         SUBSTR(test, INSTR(test || ';', ';') + 1) rest
  FROM (SELECT rowid, REPLACE(REPLACE(REPLACE(test, '[[', '['), ']]', ']'), '],[', '];[') test FROM tablename)
  UNION ALL
  SELECT rowid,
         SUBSTR(rest, 1, INSTR(rest || ';', ';') - 1),
         SUBSTR(rest, INSTR(rest || ';', ';') + 1)
  FROM cte
  WHERE LENGTH(rest) > 0
)
SELECT GROUP_CONCAT(CASE WHEN col LIKE '[''a'',%' OR col LIKE '[''b'',%' THEN col END) x,
       GROUP_CONCAT(CASE WHEN col LIKE '[''c'',%' THEN col END) y,
       GROUP_CONCAT(CASE WHEN col LIKE '[''d'',%' THEN col END) z
FROM cte
GROUP BY rowid

See the demo.
Results:

x	y	z
[‘a’,’1′],[‘b’,’2′]	[‘c’,’3′]	null
[‘a’,’2′],[‘a’,’4′]	[‘c’,’8′]	[‘d’,’3′]
[‘b’,’3′]	[‘c’,5′],[‘c’,’6′]	[‘d’,’4′]