On Fri, Apr 30, 2010 at 9:57 AM, David Lyon <david_ly...@yahoo.com> wrote:
> Thanks for everyones efforts let me expand:
>
>
> if I had many many files like this:
> http://research.stowers-institute.org/efg/ScientificSoftware/Utility/FCSExtract/CC4_067_BM.txt
>
> you see 2 columns keyword and value, the keywords would be the fields (1st
> column in the html link above) in the table while the values (second column)
> are the data I would insert.
>
> The fields will be many and include
>
> P$nR
> P$nS
> P$nB
>
> etc.
>
Will every row in your proposed table have the same P?? columns? In
other words, will the keys in each of the files that you receive be
identical? If not, you can't really have a single table with all the
rows.
If you insist on having all this normalized and in a db, you could
have a main table with the common columns, and then have a 'p_table'
with 'p_key' and 'p_val' columns with PRIMARY KEY (p_key, p_val), if
required, and then reference those values in the main table.
If you just need all the values back, instead of getting individual
values, I would store them as a hash (in Perl), and then serialize
that hash and store it in the table.
> thats why I wanted a quick way to access "select P%R from TABLE";
>
>
> thanks again
>
>
>
>
>
>
>
>
>
>
>
>
>
> ----- Original Message ----
> From: P Kishor <punk.k...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Fri, April 30, 2010 10:48:41 AM
> Subject: Re: [sqlite] select %column% from table
>
> crap! I completely misunderstood your question... be confused, and
> then ignore my reply.
>
> On Fri, Apr 30, 2010 at 9:43 AM, P Kishor <punk.k...@gmail.com> wrote:
>> On Fri, Apr 30, 2010 at 9:28 AM, David Lyon <david_ly...@yahoo.com> wrote:
>>> If I had a table called TABLE with fields P1N......P50N is there a way to
>>> select something like:
>>>
>>> "select P%N from TABLE"
>>>
>>> to return all the results from columns P1N......P50N or do I have to do it
>>> manually:
>>>
>>> "select P1N, P2N, P3N, P$nN from TABLE"
>>>
>>
>> use GLOB. See below
>>
>> Last login: Wed Apr 28 09:42:46 on console
>> punk...@lucknow ~$sqlite3
>> -- Loading resources from /Users/punkish/.sqliterc
>> SQLite version 3.6.23
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> CREATE TABLE t(a);
>> sqlite> INSERT INTO t VALUES ('P1N');
>> sqlite> INSERT INTO t VALUES ('P3N');
>> sqlite> INSERT INTO t VALUES ('P30N');
>> sqlite> INSERT INTO t VALUES ('P303N');
>> sqlite> INSERT INTO t VALUES ('P303X');
>> sqlite> INSERT INTO t VALUES ('P30Z');
>> sqlite> SELECT * FROM t;
>> a
>> ----------
>> P1N
>> P3N
>> P30N
>> P303N
>> P303X
>> P30Z
>> sqlite> SELECT * FROM t WHERE a GLOB 'P*N';
>> a
>> ----------
>> P1N
>> P3N
>> P30N
>> P303N
>> sqlite>
>>
>>
>>> I can obviously do it via scripting but wanted a more elegant way.
>>>
>>> Thanks for your help in advance.
>>>
>>>
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> -----------------------------------------------------------------------
>> Assertions are politics; backing up assertions with evidence is science
>> =======================================================================
>>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> -----------------------------------------------------------------------
> Assertions are politics; backing up assertions with evidence is science
> =======================================================================
> _______________________________________________
> 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
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
