SELECT word.cid, word.word, gloss.word
FROM (
SELECT row_number() over (order by cid) as rn, word, cid
FROM (
WITH split(word, clause, cid) AS (
SELECT '', f.cldf_analyzedWord || X'09', f.cldf_id
FROM ExampleTable AS f
UNION ALL
SELECT
substr(clause, 0, instr(clause, X'09')),
substr(clause, instr(clause, X'09') + 1),
cid
FROM split WHERE clause != ''
)
SELECT word, cid FROM split where word != '')
) AS word,
(
SELECT row_number() over (order by cid) as rn, word, cid
FROM (
WITH split(word, clause, cid) AS (
SELECT '', f.cldf_gloss || X'09', f.cldf_id
FROM ExampleTable AS f
UNION ALL
SELECT
substr(clause, 0, instr(clause, X'09')),
substr(clause, instr(clause, X'09') + 1),
cid
FROM split WHERE clause != ''
)
SELECT word, cid FROM split where word != '')
) AS gloss
WHERE word.rn = gloss.rn AND word.cid = gloss.cid
ORDER BY word.cid, word.rn;
Note: Since this recipe relies on correct word/gloss alignments for all examples, the relevant subset of examples may be filtered using the lgrConformance column.