Since SQL is designed to return rows I believe the answer is "no" since you're data is not stored in rows.
You need to do what's referred to as "normalizing" your data. If you normalize the answer becomes a natural query. create table kind (kind_id int primary key, name text); insert into kind values(1,'Bird'); insert into kind values(2,'Mammal'); insert into kind values(3,'Fish'); create table species (species_id int primary key, name text,kind_id int); insert into species values(1,'eagle',(select kind_id from kind where name='Bird')); insert into species values(2,'seagull',(select kind_id from kind where name='Bird')); insert into species values(3,'hen',(select kind_id from kind where name='Bird')); insert into species values(4,'dog',(select kind_id from kind where name='Mammal')); insert into species values(5,'cat',(select kind_id from kind where name='Mammal')); insert into species values(6,'cow',(select kind_id from kind where name='Mammal')); insert into species values(7,'rabbit',(select kind_id from kind where name='Mammal')); insert into species values(8,'tuna',(select kind_id from kind where name='Fish')); insert into species values(9,'shark',(select kind_id from kind where name='Fish')); insert into species values(10,'cod',(select kind_id from kind where name='Fish')); sqlite> select species_id,species.name,kind.name from species join kind on species.kind_id=kind.kind_id; 1|eagle|Bird 2|seagull|Bird 3|hen|Bird 4|dog|Mammal 5|cat|Mammal 6|cow|Mammal 7|rabbit|Mammal 8|tuna|Fish 9|shark|Fish 10|cod|Fish Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Jean-Denis MUYS [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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
