Блог

У меня есть таблица с текстовым столбцом в базе данных, где записи для каждой строки записываются в виде списков, например, столбца test :

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

Я хочу извлечь значения в три новых столбца x y z , где

• x содержит все значения a и b;
• y содержит все значения c;
• z содержит все значения d, например

Вот так:

      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']
 

Возможно ли это сделать с помощью инструкции SQL?

Заранее спасибо

Вам нужен рекурсивный CTE для разделения каждой строки, а затем GROUP_CONCAT() для сбора значений в соответствии с вашими требованиями:

 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
 

Посмотрите демонстрационную версию.
Результаты: