Transformation from Wide to Tall

[rigglesbee]

A	B
Bob	1, 2, 3
Sue	2, 3, 4
Jane	2

And want

A	B
Bob	1
Bob	2
Bob	3
Sue	2
Sue	3
Sue	4
Jane	2

What would be the SQL code to create this?

[June7]

Is B field a multi-value or is that just a comma separated string? The first would be easy, the second is not.

[rigglesbee]

The B field is a comma separated field unfortunately

[June7]

By comma separated field you mean just a typed text string?

If the items were selected by a multi-select listbox or combobox then that is a multi-value field.

If this is simply a text string, parsing the string to separate records can be complex because of the inconsistent structure, not every value is the same length. Can there be more than 3 items or even none?

[rigglesbee]

Correct. This is just a string. The real data is actually patch IDs, with varying lengths but will be comma delimited in a single cell.

[June7]

Since there is no way to know how many elements, a UNION query is not possible.

Only approach I can see is VBA code writing records to a temp table, essentially building the structure should have started with.