T&B wrote:
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);
The heart of your problem is that the original database has no explicit
relation between the rows that have a value in the Aisle column and the
rows that don't but should. In general, since a table is nothing more
than a set (an unordered collection) of rows this information must be
supplied by some other means (i.e. user input or another table etc).
If you always insert the rows in the order shown above you can make use
of the fact that sqlite will assign rowids in the order the rows are
created. This is using a implementation detail of sqlite to supply the
missing information. There are a couple of ways to do this that come to
mind.
create table shopping as
select
(select B.Aisle from shopping_grouped as B where B.rowid =
(select max(C.rowid) from shopping_grouped as C
where C.Aisle is not null and C.rowid < A.rowid)) as Ailse,
A.Product as Product,
A.Cost as Cost
from shopping_grouped as A
where A.product is not null;
create table shopping2 as
select
(select B.Aisle from shopping_grouped as B
where B.rowid < A.rowid and B.Aisle is not null
order by B.rowid desc
limit 1) as Aisle,
A.Product as Product,
A.Cost as Cost
from shopping_grouped as A
where A.product is not null;
This gives this result in sqlite 3.3.13
sqlite> select * from shopping;
Ailse Product Cost
---------- ---------- ----------
Dairy Milk 2.0
Dairy Cream 1.0
Dairy Cheese 3.0
Bakery Sliced 4.0
Bakery Sliced 3.0
Bakery Cake 2.0
sqlite> select * from shopping2;
Aisle Product Cost
---------- ---------- ----------
Dairy Milk 2.0
Dairy Cream 1.0
Dairy Cheese 3.0
Bakery Sliced 4.0
Bakery Sliced 3.0
Bakery Cake 2.0
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
