Re: [sqlite] how to merge data from fields... if this is possible.

[Petite Abeille]

On May 29, 2012, at 8:12 PM, Pavel Ivanov wrote:

> It's obeyed after aggregation. So the same query with GROUP BY won't
> work. Although one might write something like this:

According to the fine manual [1]:

"The order of the concatenated elements is arbitrary."

[1] http://www.sqlite.org/lang_aggfunc.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to merge data from fields... if this is possible.

[Pavel Ivanov]

> 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.

It's obeyed after aggregation. So the same query with GROUP BY won't
work. Although one might write something like this:

SELECT a.fieldA,
(SELECT group_concat(b.fieldB, ' ') FROM myTable b
  WHERE a.fieldA = b.fieldA
  ORDER BY b.rowid)
FROM myTable a
GROUP BY a.fieldA


Pavel


On Tue, May 29, 2012 at 1:33 PM, Simon Slavin  wrote:
>
> On 29 May 2012, at 6:24pm, Gert Van Assche  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

Re: [sqlite] how to merge data from fields... if this is possible.

[Black, Michael (IS)]

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

Re: [sqlite] how to merge data from fields... if this is possible.

[Simon Slavin]

On 29 May 2012, at 6:24pm, Gert Van Assche  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] how to merge data from fields... if this is possible.

[Gert Van Assche]

All,

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?

thanks for your help.


Gert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users