How to split data into individual rows using SQL in Marketing Cloud

Question

We have some data in a Marketing Cloud Data Extension that looks like this

Field A        Field B 
John           Car, Boat, House, Camper 

Field B is all one cell

We need the data to split out so it's like this into a new Data Extension

Field A        Field B 
John           Car
John           Boat
John           House
John           Camper 

The data updates daily as its feed from a website, however, we need to split it out to make it more usable for what we are trying to do.

Answer 1

So, after a bit of digging I found a hacky way of doing this via CAST AS XML and CROSS APPLY.

It is certainly not efficient and there is a roof on it, meaning it will take manual control to handle values beyond X amount.

Sample of my SQL:

SELECT    p.Email
        , p.FieldA
        , c.FieldB
FROM [testParse] p
CROSS APPLY (
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[1]','nvarchar(max)') as FieldB1
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[2]','nvarchar(max)') as FieldB2
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[3]','nvarchar(max)') as FieldB3
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[4]','nvarchar(max)') as FieldB4
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[5]','nvarchar(max)') as FieldB5
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[6]','nvarchar(max)') as FieldB6
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[7]','nvarchar(max)') as FieldB7
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[8]','nvarchar(max)') as FieldB8
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[9]','nvarchar(max)') as FieldB9
) c (FieldB)
WHERE ISNULL(c.FieldB,'') <> ''

The main part to pay attention to is the individual sections in the CROSS APPLY:

Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML).value('/x[1]','nvarchar(max)') as FieldB1

This takes your delimited list (in this case FieldB and turns it into XML via CAST and REPLACE and combining and then grabs the 'X' node value (in this case 1) and outputs it.

Creation of XML

Select  CAST('<x>' + REPLACE(p.FieldB,',','</x><x>') + '</x>' AS XML)

First step is to replace all the commas inside Field B with </x><x> to transform each item in the delimited list into an XML node.

Second step is to add <x> to the beginning of the string and </x> at the end to complete the XML.

Finally you CAST the final string as XML data type via CAST().

Output of XML Node Value

.value('/x[1]','nvarchar(max)')

This function (value()) will take the node N ('/x[1] - referenced as 1 here) and outputs it as the data value of 'nvarchar(max)'.

Now this works wonders combined with CROSS APPLY, but as I mentioned before, this has a ceiling.

For instance in my sample, any FieldB delimited strings with more than 9 items will only display the first 9, the rest will be lost.

If you have a relatively small cluster of delimited items, this should work great, but when you have dozens or hundreds of entries, this can get unwieldy. Not to mention this is not exactly efficient, so there is a significant risk of Time out as volume increases.

ALSO, do note that I have WHERE ISNULL(c.FieldB,'') <> '' This is to remove all those values that are run in the cross apply beyond the actual number of delimited items in the string.

For example, if FieldB is 1,2,3,4 - The output would only have 4 rows, not 10 with nulls for 6 of them.

Is there a better way? Most certainly. Is it available in SFMC? Not that I have been able to see. Anyone that knows differently or can better optimize what I have, please do so! I look forward to it!