Hi everyone,

I have the following problem.

I store stock - take data in a stock - take table. There is one row
for each stock item. The row has a "stock take number" field which
links it to a table storing the stock take data (when it happened).

How do I represent this as a set of columns (date)? What I would like
to do is to be able to show, (for example) the last 6 stock takes in
one view or select.

I apologise in advance if I am not explaining myself very well.

Here is the table metadata : (each record in Stock Take can have one
or many records in stock count; each record in stock count maps to
exactly one record in stock take)

CREATE TABLE "STOCK_TAKE"
(
"RECORDNO" INTEGER NOT NULL,
"START_DATETIME" TIMESTAMP,
"ENDDATETIME" TIMESTAMP,
"STOCKTAKER" VARCHAR(32),
"LOCATION" VARCHAR(3),
PRIMARY KEY ("RECORDNO")
);

CREATE TABLE "STOCK_COUNT"
(
"RECORDNO" INTEGER NOT NULL,
"STOCK_TAKE_NUMBER" INTEGER NOT NULL,
"STOCK_CODE" VARCHAR(13),
"SYS_AMOUNT" NUMERIC(13, 3),
"SYS_SOLD" NUMERIC(13, 3),
"COUNTED_AMOUNT" NUMERIC(13, 3),
"PPU" NUMERIC(15, 4),
"COST" NUMERIC(15, 4),
"VAT_AMOUNT" NUMERIC(15, 4),
"LOCATION" VARCHAR(2),
"STOCK_DEPT" VARCHAR(16),
PRIMARY KEY ("RECORDNO")
);


[Non-text portions of this message have been removed]