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!