I have a table with 2 text columns that I need to interpolate.

Row1

Col1 = Trees.#.Bud.Flower.#.Sepal

Col2 = [‘1′,’0’]

Row2

Col1 = Trees.#.Bud.Flower.#.Sepal.#.Colour

Col2 = [‘1′,’0′,’0’]

Desired results =

Row1 = Trees.1.Bud.Flower.0.Sepal

Row2 = Trees.1.Bud.Flower.0.Sepal.0.Colour

The closest I’ve gotten thus far is this…

SELECT 

    FORMAT(Node, '1','2','','','','','') 

    FROM ( 

        SELECT 

         'Trees.%s.Bud.Flower.%s.Sepal' as Node 

         )

But stumped at how to utilize my “Col2” to provide the multiple individual arguments required for the FORMAT function.

Very much hoping that there is a solution without having to process the datasets line-by-line, or getting into UDFs which I understand aren’t simple in sqlite 😞

Any help appreciated 🤞