Does this do what you want?
create table t1(rowid,fieldA,fieldB); insert into t1 values(1,'val1','This is a'); insert into t1 values(2,'val1','small'); insert into t1 values(3,'val1','test.'); insert into t1 values(4,'val2','The proof is in'); insert into t1 values(5,'val2','the pudding.'); create table t2(rowid,fieldA,fieldB); insert into t2 values(1,'val1','This is a small test.'); insert into t2 values(2,'val2','The proof is in the pudding.'); sqlite> select t2.rowid,t2.fieldB from t1,t2 where t1.fieldA=t2.fieldA; 1|val1|This is a|1|val1|This is a small test. 2|val1|small|1|val1|This is a small test. 3|val1|test.|1|val1|This is a small test. 4|val2|The proof is in|2|val2|The proof is in the pudding. 5|val2|the pudding.|2|val2|The proof is in the pudding. sqlite> select t2.rowid,t2.fieldA,t2.fieldB from t1,t2 where t1.fieldA=t2.fieldA group by t2.rowid; 1|val1|This is a small test. 2|val2|The proof is in the pudding. You may want an "order by t2.rowid" or such. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Tuesday, May 29, 2012 12:33 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] how to merge data from fields... if this is possible. On 29 May 2012, at 6:24pm, Gert Van Assche <ger...@datamundi.be> wrote: > I have a dataset that looks like this: > > rowid ; fieldA ; fieldB > 1 ; val1 ; This is a > 2 ; val1 ; small > 3 ; val1 ; test. > 4 ; val2 ; The proof is in > 5 ; val2 ; the pudding. > > > And I would like to merge all values in fieldB when the value in fieldA is > the same. > > rowid ; fieldA ; fieldB > 1 ; val1 ; This is a small test. > 2 ; val2 ; The proof is in the pudding. > > > Question: can you do this with an SQL query? I haven't tried this but I'd try using the group_concat() function. For instance SELECT group_concat(fieldB,' ') FROM myTable WHERE fieldA = 'val1' ORDER BY rowid Might get one of the values you wanted. One concern is that I don't remember whether the ORDER BY clause is obeyed before or after aggregation. But if the above works the way you want, then try and get all the values you want in one SELECT using GROUP BY. Simon. _______________________________________________ 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
