Not much help but this removes the multiplication: SELECT B1.B + B2.B + B3.B + B4.B FROM (SELECT 0 AS B UNION SELECT 1 AS B) AS B1, (SELECT 0 AS B UNION SELECT 2 AS B) AS B2, (SELECT 0 AS B UNION SELECT 4 AS B) AS B3, (SELECT 0 AS B UNION SELECT 8 AS B) AS B4
On 10/15/2010 3:00 PM, Max Vlasov wrote: > Hi, > from time to time I try to solve well-known task of making rows from a > comma-delimited list and the best I could do was this: > - create a user-function returning zero-based Nth item from the list (let's > call it GetItemFromSet) > - make a complex query like this (this one allows up to 16 elements in the > list, can be expanded with similar selects) > > SELECT Trim(GetItemFromSet(Value, '23, 14, 1, 7, 9')) Item FROM > (SELECT B1.B*1 + B2.B*2 + B3.B*4 + B4.B*8 AS VALUE FROM > (SELECT 0 AS B UNION SELECT 1 AS B) AS B1, > (SELECT 0 AS B UNION SELECT 1 AS B) AS B2, > (SELECT 0 AS B UNION SELECT 1 AS B) AS B3, > (SELECT 0 AS B UNION SELECT 1 AS B) AS B4, > ) WHERE NOT (Item Is Null) > > Is there a way to implement something more elegant or at least to improve > this approach. For example, the query for 16 bit limit will have 16 selects > and bigger expression and also will iterate through all cross join output > when we actually only have 5 items in the list. > > Max > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
