Re: [sqlite] splitting field data

[cmartin]

On Wed, 18 Jun 2008, P Kishor wrote:

>>  CREATE TABLE foo (ID INTEGER PRIMARY KEY, ITEMS);
>>  INSERT INTO foo VALUES(1,item1;item2;item3);
>>  INSERT INTO foo VALUES(2,item1;item4);
>>  INSERT INTO foo VALUES(3,item5;item3;item7);
>
> The above is incorrect SQL. If you run the above INSERT commands, you
> will get an error because the ITEMS values should be quoted with
> single quotes. It really should be
>
> INSERT INTO foo VALUES (1, 'item1;item2;item3');

Sorry, I was just using this for illustration of the data structure, I 
should have shown it with the (implied) quotes.

> Write a custom function. This kind of stuff is trivial to do in a
> programming language, but SQL is not really the best for this. If you
> are getting the SQL statements to construct your db, best is to
> preprocess that with Perl or Python or whatever you desire.

I am not getting the SQL statements, I am getting the sqlite database 
already in the format shown. I can definitely re-shape it via external 
functions/processing, but I wondered if anyone had any SQL-based 
solutions. I am often pleasantly surprised to find that SQL-syntax exists 
for doing something I didn't think SQL was capable of.

Chris

On Wed, 18 Jun 2008, P Kishor wrote:

> On 6/17/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>> I have a table that contains 2 fields: ID, ITEMS, as:
>>
>>  CREATE TABLE foo (ID INTEGER PRIMARY KEY, ITEMS);
>>  INSERT INTO foo VALUES(1,item1;item2;item3);
>>  INSERT INTO foo VALUES(2,item1;item4);
>>  INSERT INTO foo VALUES(3,item5;item3;item7);
>>
>
> The above is incorrect SQL. If you run the above INSERT commands, you
> will get an error because the ITEMS values should be quoted with
> single quotes. It really should be
>
> INSERT INTO foo VALUES (1, 'item1;item2;item3');
>
>>  As shown, ITEMS is comprised of individual data items separated by
>>  semicolons. For some purposes, this concatenated form suits the purpose,
>>  but for other purposes I need to split these items into individual data
>>  points. That is, I want to split the ITEMS (at the semicolons) into
>>  separate rows in a new table FOO2, having fields ID and SINGLEITEM that
>>  looks like this:
>>
>>  ID, SINGLEITEM
>>  1,item1
>>  1,item2
>>  1,item3
>>  2,item1
>>  2,item4
>>  3,item5
>>  3,item3
>>  3,item7
>>
>>  Any suggestions of how to do this in SQLite?
>>
>
>
> Write a custom function. This kind of stuff is trivial to do in a
> programming language, but SQL is not really the best for this. If you
> are getting the SQL statements to construct your db, best is to
> preprocess that with Perl or Python or whatever you desire.
>
> -- 
> Puneet Kishor
> ___
> 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] splitting field data

[P Kishor]

On 6/17/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> I have a table that contains 2 fields: ID, ITEMS, as:
>
>  CREATE TABLE foo (ID INTEGER PRIMARY KEY, ITEMS);
>  INSERT INTO foo VALUES(1,item1;item2;item3);
>  INSERT INTO foo VALUES(2,item1;item4);
>  INSERT INTO foo VALUES(3,item5;item3;item7);
>

The above is incorrect SQL. If you run the above INSERT commands, you
will get an error because the ITEMS values should be quoted with
single quotes. It really should be

INSERT INTO foo VALUES (1, 'item1;item2;item3');

>  As shown, ITEMS is comprised of individual data items separated by
>  semicolons. For some purposes, this concatenated form suits the purpose,
>  but for other purposes I need to split these items into individual data
>  points. That is, I want to split the ITEMS (at the semicolons) into
>  separate rows in a new table FOO2, having fields ID and SINGLEITEM that
>  looks like this:
>
>  ID, SINGLEITEM
>  1,item1
>  1,item2
>  1,item3
>  2,item1
>  2,item4
>  3,item5
>  3,item3
>  3,item7
>
>  Any suggestions of how to do this in SQLite?
>


Write a custom function. This kind of stuff is trivial to do in a
programming language, but SQL is not really the best for this. If you
are getting the SQL statements to construct your db, best is to
preprocess that with Perl or Python or whatever you desire.

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

Re: [sqlite] splitting field data

[cmartin]

On Tue, 17 Jun 2008, Rich Shepard wrote:

>   You ought to normalize your data. Consider (and this is just off the top
> of my head) two tables: one to hold categories and their descriptions, the
> other to hold categories and items. Then you can select either specific
> items, all items, or groups of items collected as you wish.

Right, I realize the data should be normalized, but the data I have shown 
is how it arrives in the database from an external source. That data is my 
starting point, and I need to re-shape it for a specific need. I am just 
looking for some efficient syntax to do this re-shaping.

Chris


> On Tue, 17 Jun 2008, [EMAIL PROTECTED] wrote:
>
>> As shown, ITEMS is comprised of individual data items separated by
>> semicolons. For some purposes, this concatenated form suits the purpose,
>> but for other purposes I need to split these items into individual data
>> points. That is, I want to split the ITEMS (at the semicolons) into
>> separate rows in a new table FOO2, having fields ID and SINGLEITEM that
>> looks like this:
>>
>> ID, SINGLEITEM
>> 1,item1
>> 1,item2
>> 1,item3
>> 2,item1
>> 2,item4
>> 3,item5
>> 3,item3
>> 3,item7
>>
>> Any suggestions of how to do this in SQLite?
>
> Chris,
>
>   You ought to normalize your data. Consider (and this is just off the top
> of my head) two tables: one to hold categories and their descriptions, the
> other to hold categories and items. Then you can select either specific
> items, all items, or groups of items collected as you wish.
>
> Rich
>
> -- 
> Richard B. Shepard, Ph.D.   |  IntegrityCredibility
> Applied Ecosystem Services, Inc.|Innovation
>  Voice: 503-667-4517  Fax: 503-667-8863
> ___
> 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] splitting field data

[Rich Shepard]

On Tue, 17 Jun 2008, [EMAIL PROTECTED] wrote:

> As shown, ITEMS is comprised of individual data items separated by
> semicolons. For some purposes, this concatenated form suits the purpose,
> but for other purposes I need to split these items into individual data
> points. That is, I want to split the ITEMS (at the semicolons) into
> separate rows in a new table FOO2, having fields ID and SINGLEITEM that
> looks like this:
>
> ID, SINGLEITEM
> 1,item1
> 1,item2
> 1,item3
> 2,item1
> 2,item4
> 3,item5
> 3,item3
> 3,item7
>
> Any suggestions of how to do this in SQLite?

Chris,

   You ought to normalize your data. Consider (and this is just off the top
of my head) two tables: one to hold categories and their descriptions, the
other to hold categories and items. Then you can select either specific
items, all items, or groups of items collected as you wish.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] splitting field data

[cmartin]

I have a table that contains 2 fields: ID, ITEMS, as:

CREATE TABLE foo (ID INTEGER PRIMARY KEY, ITEMS);
INSERT INTO foo VALUES(1,item1;item2;item3);
INSERT INTO foo VALUES(2,item1;item4);
INSERT INTO foo VALUES(3,item5;item3;item7);

As shown, ITEMS is comprised of individual data items separated by 
semicolons. For some purposes, this concatenated form suits the purpose, 
but for other purposes I need to split these items into individual data 
points. That is, I want to split the ITEMS (at the semicolons) into 
separate rows in a new table FOO2, having fields ID and SINGLEITEM that 
looks like this:

ID, SINGLEITEM
1,item1
1,item2
1,item3
2,item1
2,item4
3,item5
3,item3
3,item7

Any suggestions of how to do this in SQLite?

Chris Martin
UNC-CH


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