Text To Columns

[#1]

I have a column of data with values separated by commas.   I have  few tricks up my sleeve but they require a too many temp resident loads.  I was hoping to find the most optimized solution for this type of conversion:

Source table:

Unique ID        Guests

1                     Joe Smith, Amy Lee, John Deer

2                     Amy Lee, Jane Doe

3                    Jerry Jones, John Deer, Joe Smith

4                    Jack Rabbit, Cecil Williams

Destination Table:

I want to load it as

ID                Guest

1                  Joe Smith

1                  Amy Lee

1                  John Deer

2                  Amy Lee

2                  Jane Doe

3                  Jerry Jones

3                  John Deer

3                  Joe Smith

4                  Jack Rabbit

4                  Cecil Williams

[jagan · Accepted solution]

Hi,

Try like this

Data:

Load

UniqueID,

SubField(Guests, ',') AS Guests

FROM DataSource;

Regards,

Jagan.

[#3]

Thank you!  That worked perfectly as long as run it before I load the fact table.  for some reason after I load the fact table it only gives me the last Guest following the last comma. 

[jagan]

Hi,

It is difficult to tell the issue, can you attach some sample values and sample file.

Regards,

Jagan.

[#5]

Thank you so much for your help.  It may be the way I was loading my fact table.  But moving it to the initial load and creating the Guest column from the original QVD instead of the resident table, made it work.  I am all set now.  I am sure it must have been the fact table.  I am cleaning that up now.

You solved my problem.  thank you very much