On Sat, Apr 5, 2014 at 11:46 AM, RSmith <rsm...@rsweb.co.za> wrote: > WITH csvrec(i,l,c,r) AS ( > SELECT tmpcsv.ID, 1, colCSV||',', '' FROM tmpcsv > UNION ALL > SELECT i, > instr(c,',') AS vLen, > substr(c,instr(c,',')+1) AS vRem, > substr(c,1,instr(c,',')-1) AS vCSV > FROM csvrec > WHERE vLen>0 > ) > SELECT t.ID, t.colA, rt.r FROM tmpcsv AS t, csvrec AS rt > WHERE t.ID=rt.i AND rt.r<>'' > ORDER BY t.ID > LIMIT 100
Very interesting. Thanks for sharing that. But can this CTE be turned into a view? Or does one need to retype the whole "algorithm" every time one needs "join" on the "virtual" unrolled CSV field table? And assuming such a "CTE view" can de defined, what if one selects from the "CTE view" with a WHERE clause, to get only the CSV fields of a single row of scvrec, would that prevent the whole "tmpcsv" result-set for every row of csvrec to be generated? If the answer to either question above is true, then a specialized vtable would be both more convenient and faster, no? Your CTE has the great benefit to work out of the box though, unlike a vtable, so it's a great example nonetheless. Thanks again for that. --DD _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
