Subject Converting FireBird Table from horizontal to vertical
Author Robbie
I've a large file which has 32 fields. The field names follow this pattern :

Features Features_1 Features_2 ... Features_9 Features_10 ... Features_30

For each record, not all of them have data stored in all 30 features fields. I figure there's probably 50 wasted space in this table. ( Each field is a tinyint. ) The first field is a unique identifier.

I have created a new table and the 1st of 2 fields will contain a copy of the identifier from the first table. The 2nd field will contain a list of features.

Therefore, if there are 15 features recorded in the 1st table for the 1st identifier, then I want to end up with 15 records in the 2nd table.

I've created a query to transfer all the ID's which have data in the FEATURES column. After running the query, I edited the query so that it would transfer the ID's which have valid data in FEATURES_2 column, edited it again for FEATURES_3, and so on, till I'd edited and executed the query 30 times.

Having done it the "hard way", I now wish to make this into a procedure ... and since I'm such a rookie, I've not yet been able to figure out how to write a short procedure, with a WHILE DO loop, to transfer one FEATURES column at a time.

I'm hoping that someone here will be willing and able to point me in the right direction.

I loved the days when I worked with Paradox and it would record macros, which I'd cut/paste, edit and combine and VOILA, it was done :)

Respectfully yours,