split_csv_string.sql

select 
orders.SubscriberKey, orders.OrderID
from (

    SELECT
    a.SubscriberKey
    , SUBSTRING(a.OrderIDs, 1, ISNULL(p1 - 1, LEN(a.OrderIDs))) OrderID
    from AllOrders a
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs), 0)) b(p1)

    union 

    SELECT
    a.SubscriberKey
    , SUBSTRING(a.OrderIDs, p1 + 1, ISNULL(p2, LEN(a.OrderIDs) + 1) - p1 - 1) OrderID
    from AllOrders a
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs), 0)) b(p1)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p1 + 1), 0)) c(p2)

    union 

    SELECT
    a.SubscriberKey
    , SUBSTRING(a.OrderIDs, p2 + 1, ISNULL(p3, LEN(a.OrderIDs) + 1) - p2 - 1) OrderID
    from AllOrders a
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs), 0)) b(p1)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p1 + 1), 0)) c(p2)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p2 + 1), 0)) d(p3)

    union 

    SELECT
    a.SubscriberKey
    , SUBSTRING(a.OrderIDs, p3 + 1, ISNULL(p4, LEN(a.OrderIDs) + 1) - p3 - 1) OrderID
    from AllOrders a
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs), 0)) b(p1)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p1 + 1), 0)) c(p2)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p2 + 1), 0)) d(p3)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p3 + 1), 0)) e(p4)

    union 

    SELECT
    a.SubscriberKey
    , SUBSTRING(a.OrderIDs, p4 + 1, ISNULL(p5, LEN(a.OrderIDs) + 1) - p4 - 1) OrderID
    from AllOrders a
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs), 0)) b(p1)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p1 + 1), 0)) c(p2)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p2 + 1), 0)) d(p3)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p3 + 1), 0)) e(p4)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p4 + 1), 0)) f(p5)

    union 

    SELECT
    a.SubscriberKey
    , SUBSTRING(a.OrderIDs, p5 + 1, ISNULL(p6, LEN(a.OrderIDs) + 1) - p5 - 1) OrderID
    from AllOrders a
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs), 0)) b(p1)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p1 + 1), 0)) c(p2)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p2 + 1), 0)) d(p3)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p3 + 1), 0)) e(p4)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p4 + 1), 0)) f(p5)
    CROSS APPLY (select NULLIF(CHARINDEX(',', a.OrderIDs, p5 + 1), 0)) g(p6)

) orders 
where orders.OrderID is not null