Re: [sqlite] graph question
On Mon, Jun 9, 2008 at 3:49 PM, Bruce Robertson <[EMAIL PROTECTED]> wrote: >> select f || ' ' || group_concat(t, ' ') >> from w >> where f in (1, 2, 3); >> group by f; >> >> HTH >> Dennis > > Can you explain that? > > What's the || ' ' || part? || is the string concat operator. e.g. SELECT "foo" || "bar"; will print: foobar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] graph question
> David Baird wrote: >> >> Okay, just built SQLite 3.5.9 and group_concat does in fact work: >> >> select group_concat(t, ' ') from w where f=1; > > You forgot the parent value at the beginning. Also, the OP may want to > do this for several parents which can be accomplished by grouping the > results. > > select f || ' ' || group_concat(t, ' ') > from w > where f in (1, 2, 3); > group by f; > > HTH > Dennis Can you explain that? What's the || ' ' || part? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] graph question
sqlite>select level from sqlGuruOMeter where name="Ron Wilson"; 2 Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P Sent: Monday, June 09, 2008 1:49 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] graph question Wicked. Thanks David and Dennis! And this works like a charm for all parents: select f || ' ' || group_concat(t, ' ') from w group by f; This pushes me up to a '2' on the SQL Guru Meter. RW SQLGuru-O-Meter |0-+--510| Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Monday, June 09, 2008 1:41 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] graph question David Baird wrote: > > Okay, just built SQLite 3.5.9 and group_concat does in fact work: > > select group_concat(t, ' ') from w where f=1; You forgot the parent value at the beginning. Also, the OP may want to do this for several parents which can be accomplished by grouping the results. select f || ' ' || group_concat(t, ' ') from w where f in (1, 2, 3); group by f; HTH Dennis ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] graph question
On Mon, Jun 9, 2008 at 11:40 AM, Dennis Cote <[EMAIL PROTECTED]> wrote: > David Baird wrote: >> >> Okay, just built SQLite 3.5.9 and group_concat does in fact work: >> >> select group_concat(t, ' ') from w where f=1; > > You forgot the parent value at the beginning. Also, the OP may want to > do this for several parents which can be accomplished by grouping the > results. > > select f || ' ' || group_concat(t, ' ') > from w > where f in (1, 2, 3); > group by f; Ahh, right. Thanks for fixing that :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] graph question
Wicked. Thanks David and Dennis! And this works like a charm for all parents: select f || ' ' || group_concat(t, ' ') from w group by f; This pushes me up to a '2' on the SQL Guru Meter. RW SQLGuru-O-Meter |0-+--510| Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Monday, June 09, 2008 1:41 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] graph question David Baird wrote: > > Okay, just built SQLite 3.5.9 and group_concat does in fact work: > > select group_concat(t, ' ') from w where f=1; You forgot the parent value at the beginning. Also, the OP may want to do this for several parents which can be accomplished by grouping the results. select f || ' ' || group_concat(t, ' ') from w where f in (1, 2, 3); group by f; HTH Dennis ___ 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] graph question
Yes. I have been lurking here long enough to hear that response 100x - and it is a fair answer. Thanks! RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Monday, June 09, 2008 1:09 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] graph question Wilson, Ron P <[EMAIL PROTECTED]> wrote: > select t from w where f=1; > 2 > 3 > 4 > 5 > 6 > 7 > > I would like the output to look like this: > > 1 2 3 4 5 6 7 > > i.e. parent child1 child2 ... childN SQL is not formatting or reporting library. It gives you raw data, and it's up to your application to build a presentation from it. Igor Tandetnik ___ 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] graph question
David Baird wrote: > > Okay, just built SQLite 3.5.9 and group_concat does in fact work: > > select group_concat(t, ' ') from w where f=1; You forgot the parent value at the beginning. Also, the OP may want to do this for several parents which can be accomplished by grouping the results. select f || ' ' || group_concat(t, ' ') from w where f in (1, 2, 3); group by f; HTH Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] graph question
On Mon, Jun 9, 2008 at 11:19 AM, David Baird <[EMAIL PROTECTED]> wrote: > On Mon, Jun 9, 2008 at 11:09 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: >> Wilson, Ron P >> <[EMAIL PROTECTED]> >> wrote: >>> select t from w where f=1; >>> 2 >>> 3 >>> 4 >>> 5 >>> 6 >>> 7 >>> >>> I would like the output to look like this: >>> >>> 1 2 3 4 5 6 7 >>> >>> i.e. parent child1 child2 ... childN >> >> SQL is not formatting or reporting library. It gives you raw data, and >> it's up to your application to build a presentation from it. >> > > Actually, it seems that the group_concat aggregation function should > do the trick: > >http://www.sqlite.org/lang_aggfunc.html > > But, my SQLite (3.4.2) on Ubuntu seems to be missing the group_concat > function though :-( Okay, just built SQLite 3.5.9 and group_concat does in fact work: select group_concat(t, ' ') from w where f=1; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] graph question
On Mon, Jun 9, 2008 at 11:09 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Wilson, Ron P > <[EMAIL PROTECTED]> > wrote: >> select t from w where f=1; >> 2 >> 3 >> 4 >> 5 >> 6 >> 7 >> >> I would like the output to look like this: >> >> 1 2 3 4 5 6 7 >> >> i.e. parent child1 child2 ... childN > > SQL is not formatting or reporting library. It gives you raw data, and > it's up to your application to build a presentation from it. > Actually, it seems that the group_concat aggregation function should do the trick: http://www.sqlite.org/lang_aggfunc.html But, my SQLite (3.4.2) on Ubuntu seems to be missing the group_concat function though :-( -David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] graph question
Wilson, Ron P <[EMAIL PROTECTED]> wrote: > select t from w where f=1; > 2 > 3 > 4 > 5 > 6 > 7 > > I would like the output to look like this: > > 1 2 3 4 5 6 7 > > i.e. parent child1 child2 ... childN SQL is not formatting or reporting library. It gives you raw data, and it's up to your application to build a presentation from it. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] graph question
Given the following: create table w (f, t); begin; insert into w (f, t) values (1, 2); insert into w (f, t) values (1, 3); insert into w (f, t) values (1, 4); insert into w (f, t) values (1, 5); insert into w (f, t) values (1, 6); insert into w (f, t) values (1, 7); ... commit; select t from w where f=1; 2 3 4 5 6 7 I would like the output to look like this: 1 2 3 4 5 6 7 i.e. parent child1 child2 ... childN is there any way to do that with sql or should I just do it in my program? RW --Tyco Electronics-- Ron Wilson, S/W Systems Engineer III 434.455.6453 tycoelectronics.com Important: This electronic mail message and any attached files contain information intended for the exclusive use of the individual or entity to which it is addressed and may contain information that is proprietary, privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information may be subject to legal restriction or sanction. Please notify the sender, by electronic mail or telephone, of any unintended recipients and delete the original message without making any copies. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
