Perfect! I guessed this could be achieved with a recursive CTE but I could not find one that would produce my desired view. Your CTE is simply a table of strings keyed by the length and then you join it with the stringlengths table to create the final view. Thanks
On 22 November 2017 at 23:55, R Smith <rsm...@rsweb.co.za> wrote: > > On 2017/11/22 11:56 PM, Shane Dev wrote: > >> Let's say I have a table of stringlengths - >> >> sqlite>select * from stringlengths; >> length >> 4 >> 1 >> 9 >> ... >> >> Can I create a view xstrings containing strings (for example of char 'x') >> with the lengths specified in stringlengths? >> > > Pretty easily: > > -- SQLite version 3.20.1 [ Release: 2017-08-24 ] on SQLitespeed > version 2.0.2.4. > > -- Script Items: 5 Parameter Count: 0 > -- 2017-11-23 00:53:19.542 | [Info] Script Initialized, Started > executing... > -- ============================================================ > ==================================== > > > CREATE TABLE SL(id INTEGER PRIMARY KEY, sLength INT); > > INSERT INTO SL(sLength) VALUES (4),(1),(9),(72),(5); > > SELECT * FROM SL; > > -- id | sLength > -- ------------ | ------- > -- 1 | 4 > -- 2 | 1 > -- 3 | 9 > -- 4 | 72 > -- 5 | 5 > > > CREATE VIEW xstrings AS > WITH SB(i,xs) AS ( > SELECT 0, '' > UNION ALL > SELECT i+1, xs||'x' FROM SB WHERE i<=(SELECT MAX(sLength) FROM SL) > ) > SELECT xs > FROM SL,SB > WHERE SB.i = SL.sLength > ORDER BY SL.id > ; > > SELECT * FROM xstrings; > > > -- xs > -- ------------------------------------------------------------ > ------------ > -- xxxx > -- x > -- xxxxxxxxx > -- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx > xxxxxxxxxxxx > -- xxxxx > > -- Script Stats: Total Script Execution Time: 0d 00h 00m and > 00.031s > -- Total Script Query Time: 0d 00h 00m and > 00.001s > -- Total Database Rows Changed: 5 > -- Total Virtual-Machine Steps: 2250 > -- Last executed Item Index: 5 > -- Last Script Error: > -- ------------------------------------------------------------ > ------------------------------------ > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
