On 6/17/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I have a table that contains 2 fields: ID, ITEMS, as: > > CREATE TABLE foo (ID INTEGER PRIMARY KEY, ITEMS); > INSERT INTO foo VALUES(1,item1;item2;item3); > INSERT INTO foo VALUES(2,item1;item4); > INSERT INTO foo VALUES(3,item5;item3;item7); >
The above is incorrect SQL. If you run the above INSERT commands, you will get an error because the ITEMS values should be quoted with single quotes. It really should be INSERT INTO foo VALUES (1, 'item1;item2;item3'); > As shown, ITEMS is comprised of individual data items separated by > semicolons. For some purposes, this concatenated form suits the purpose, > but for other purposes I need to split these items into individual data > points. That is, I want to split the ITEMS (at the semicolons) into > separate rows in a new table FOO2, having fields ID and SINGLEITEM that > looks like this: > > ID, SINGLEITEM > 1,item1 > 1,item2 > 1,item3 > 2,item1 > 2,item4 > 3,item5 > 3,item3 > 3,item7 > > Any suggestions of how to do this in SQLite? > Write a custom function. This kind of stuff is trivial to do in a programming language, but SQL is not really the best for this. If you are getting the SQL statements to construct your db, best is to preprocess that with Perl or Python or whatever you desire. -- Puneet Kishor _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
