Need to parse one column into multiple fields..
Posted on 2010-09-07
I have a table that was passed to me by a colleague. The table only has two columns, the first is just an index (specifying order) and the second contains the contents of a flat file that contains multiple rows which represent ONE record where each row has a text field with a keyword followed by a value.
IDX | KEYS
1 | STARTKEY 333
2 | KEY_A 4000
3 | KEY_B 5555
4 | KEY_C 6656
6 | STARTKEY 766
7 | KEY_A 3000
8 | KEY_B 2363
I would like to parse through every "line" of this table and pull out the real rows to insert into another table.
For instance, the final table would look like this:
START_KEY | KEY_A | KEY_B | KEY_C | .....
333 | 4000 | 5555 | 6656 | ....
766 | 3000 | 2363 | .....
All key names are known, there are about 30 of them. I guess I need something to first loop through all records in the source table and then for each row, look at the key names and record their values and also, if the key name changes to "START_KEY", then write out all of the keys found since the last "START_KEY"...
In VB, I would probably start by defining all known keys like this:
But that's where I'm stuck... How do I "select * from SORUCE_TABLE" and then feed the data to a parser that will split the data and record the values to "insert" a row in my other table?