On 12 sept. 2011, at 13:26, Black, Michael (IS) wrote: Since SQL is designed to return rows I believe the answer is "no" since you're data is not stored in rows.
I suspected such a limitation. Thanks for the confirmation. (though I question the justification: "SQL is designed to return rows" is precisely the reason why one could hope it could "return rows from the results of a [split] function"). You need to do what's referred to as "normalizing" your data. That's one goal of the migration. Back to programming. JD […] Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org<mailto:sqlite-users-boun...@sqlite.org> [sqlite-users-boun...@sqlite.org<mailto:sqlite-users-boun...@sqlite.org>] on behalf of Jean-Denis MUYS [jdm...@kleegroup.com<mailto:jdm...@kleegroup.com>] Sent: Monday, September 12, 2011 3:28 AM To: General Discussion of SQLite Database Subject: EXT :[sqlite] splitting a line to produce several lines? I am migrating data from a database which has a table that describes items by "kinds". Instances of those "kinds" are stored in one column of this table, listing each instance's id, with all ids separated by a slash "/". Of course, this is a very poor design. The target system has a proper table for instances, with a one-to-many relationship between kinds and instances. My question is: is there a pure SQL way to split the instances string, and generate the instance lines from the instance list string? contrived example: Table Kind: id name instances 1 Bird 'eagle/seagull/hen' 2 Mammal 'dog/cat/cow/rabbit' 3 Fish 'tuna/shark/cod' Possible outcome: Table Instances: id name kind_id 1 eagle 1 2 seagull 1 3 hen 1 4 dog 2 5 cat 2 6 cow 2 7 rabbit 2 8 tuna 3 9 shark 3 10 cod 3 (the instance id is not especially important. it could as well be the concatenation of the kind id/name and the instance name) Of course, I could write a program to do that, but if I could do it in a few SQL statements, it would happen earlier… Thanks, Jean-Denis _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
