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
