Thanks Puneet for your response.
this is a programming problem and not necessarily a SQLite problem.
Well, I can program it, but hope that instead there's a way to do it
within SQL.
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?
It comes as a CSV or tabbed text file, then into a table in my
database. So, it's in a table in my database, eg:
CREATE TABLE Shopping_Grouped(Aisle TEXT, Product TEXT, Cost REAL)
But the Aisle entries are in records by themselves, and apply to the
subsequent records containing Product and Cost, for example:
INSERT INTO Shopping_Grouped(Aisle) VALUES('Dairy');
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Milk', 2);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cream', 1);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cheese', 3);
INSERT INTO Shopping_Grouped(Aisle) VALUES('Bakery');
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Sliced', 4);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Sliced', 3);
INSERT INTO Shopping_Grouped(Product, Cost) VALUES('Cake', 2);
But I want to get it into this schema:
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Milk', 2);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Cream', 1);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Dairy', 'Cheese', 3);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Sliced',
4);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Sliced',
3);
INSERT INTO Shopping(Aisle, Product, Cost) VALUES('Bakery', 'Cake', 2);
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);
It's the "normalizing" that I'm asking how to do, via SQL (ie clever
SELECT statements).
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
That's the reverse of what I need. The data is already in this final
form and I need to "unscramble" it.
you will have to figure out how to get your source data into the
SQLite schema you create
It's already in the schema I outlined above. But how to get it into
the schema I want, is my question.
but that should be trivial depending on your programming environment.
But is it possible via SQL?
Thanks,
Tom
----
From: T&B <[EMAIL PROTECTED]>
Date: 14 March 2007 3:07:24 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Degrouping, desummarizing or integrating headings
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
Thanks,
Tom
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
