sqlite使用substr和intr实现行转列？

源表：detail

处理效果：

sql语句：

with recursive
    func(f, smcl_num) as (select part, length(part) - length(replace(part, ';', ''))
                          from chq3g4
                          union
                          select ltrim(ltrim(f, substr(f, 1, instr(f, ';') - 1)), ';'), smcl_num - 1
                          from func
                          where smcl_num > 0),
    detail as (select case
                          when instr(f, ';') = 0 then f
                          else substr(f, 1, instr(f, ';') - 1)
                          end col1
               from func)
SELECT substr(col1, 1, instr(col1, ':')-1) AS col1,
       substr(substr(col1, instr(col1, ':')+1), 1, instr(substr(col1, instr(col1, ':')+1), ':')-1) AS col2,
--        substr(substr(col1, instr(col1, ':')+1), instr(substr(col1, instr(col1, ':')+1), ':')+1) AS col31,
       substr(substr(substr(col1, instr(col1, ':')+1), instr(substr(col1, instr(col1, ':')+1), ':')+1), 1, instr(substr(substr(col1, instr(col1, ':')+1), instr(substr(col1, instr(col1, ':')+1), ':')+1), ':')-1) AS col3,
       substr(substr(substr(col1, instr(col1, ':')+1), instr(substr(col1, instr(col1, ':')+1), ':')+1),instr(substr(substr(col1, instr(col1, ':')+1), instr(substr(col1, instr(col1, ':')+1), ':')+1),':') + 1) AS col4
from detail;

原理：
利用现有的字符串处理函数想尽办法进行处理。