Reverse pivots with multiple columns - Transform efficiently without lots of tables

Hi,

I have a data table from an external source with two fields. Field one is a unique ID. Field 2 is a list of categories in the format "category1; category2; category 3..." There can be just one category or many. They are always seperated by a semicolon but not always sequential.

Example:

001           category1; category2

002           category2; category5

003           category18

004           category23; category1; category11; category6

I would like to tranform this table into an easier format to work with such as:

001           category1

001           category2

002           category2

002           category5

003           category18

004           category23

004           category1

004           category11

004           category6

However the way I'm currently doing these feel really ineffeicent. I seperate the category field delimited by semi collon, giving me many collumns: "category 1, category 2...". Most of the values in the later collumns are nulls but some rows have a large number of categories. I then duplicate the table once for each new collumn, and delete all collumns in each duplicate except one.

ie 'table 1' has only [ID] and [category 1], 'table 2' has only [ID] and [category2]...

Once I have a table for every collumn I apend all the tables into the final result.

This has three main problems:

• my dataset now has a large number of tables which I don't want (I only want to refernce the final apended table.)
• the data is not auto refreshing - if an extra category is added in the base data which exceeds my number of split collumns it won't be picked up.
• It takes absolutey ages to duplicate and sift tables like this. 

I would really appreciate any better solution than the one I'm using. Thanks!