Skip to content

Instantly share code, notes, and snippets.

@zuzannamj
Last active March 10, 2023 13:53
Show Gist options
  • Save zuzannamj/d1d557e76ee5bf4f94f53afc82140f20 to your computer and use it in GitHub Desktop.
Save zuzannamj/d1d557e76ee5bf4f94f53afc82140f20 to your computer and use it in GitHub Desktop.
SELECT p.SubscriberKey
, c.OrderIDs
FROM [AllOrders] p
CROSS APPLY (
Select CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[1]','nvarchar(max)') as OrderIDs1
UNION ALL
Select CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[2]','nvarchar(max)') as OrderIDs2
UNION ALL
Select CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[3]','nvarchar(max)') as OrderIDs3
UNION ALL
Select CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[4]','nvarchar(max)') as OrderIDs4
UNION ALL
Select CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[5]','nvarchar(max)') as OrderIDs5
UNION ALL
Select CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[6]','nvarchar(max)') as OrderIDs6
UNION ALL
Select CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[7]','nvarchar(max)') as OrderIDs7
UNION ALL
Select CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[8]','nvarchar(max)') as OrderIDs8
UNION ALL
Select CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[9]','nvarchar(max)') as OrderIDs9
) c (OrderIDs)
WHERE ISNULL(c.OrderIDs,'') <> ''
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment