On 3/13/07, T&B <[EMAIL PROTECTED]> wrote:
Hi All,I come across a fair bit of source data in summary format, where the one field's values are used as a heading for a group of records for which it applies. For instance, this shopping list: Aisle Product Cost Dairy Milk $2 Cream $1 Cheese $3 Bakery Sliced $4 Rolls $3 Cake $2 How can I select the data from that table of 8 records so that the result is this table of 6 records?: Aisle Product Cost Dairy Milk $2 Dairy Cream $1 Dairy Cheese $3 Bakery Sliced $4 Bakery Rolls $3 Bakery Cake $2
this is a programming problem and not necessarily a SQLite problem. Nevertheless, you don't specify how you are getting this summary data... are they in a database? are they just a text file? Are they XML? You could create a schema out of this normalizing aisles and products CREATE TABLE aisles (aisle_id, aisle_name); CREATE TABLE products (product_id, product_name, product_cost, aisle_id); and go from there with SELECT a.aisle_name, p.product_name, p.product_cost FROM products p JOIN aisles a ON p.aisle_id = a.aisle_id you will have to figure out how to get your source data into the SQLite schema you create, but that should be trivial depending on your programming environment. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ --------------------------------------------------------------------- collaborate, communicate, compete ===================================================================== ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
