We are having a field called - ‘VisitOverdueMailingList__c’ on the Account object populated with comma separated values. The field contains data in the following format (only for certain Accounts that satisfy certain conditions) :
Division, Business Unit, User Id, Email Division, Business Unit, User Id, Email Division, Business Unit, User Id, Email …
The length of the Division is 2, Business Unit is 4, User Id is 18. The email length of course varies. So the input DE would be like :
This field gets synchronized into Marketing Cloud. The problem is this field can have different number of rows of data based on the Account on which it exists. I am trying to process this field in order to split those values into different column using SQL.
So the final Data Extension format that I want to achieve is as below :
Taking reference from this stack exchange question : Exact Target SQL find two values within a string I am trying to construct query with 'Case' but then I am not sure how to handle the varying number of rows in this field.
Any Idea Or Solutions that I can try out on Marketing Cloud are welcome.