Sample table:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
)

INSERT Testdata SELECT 1,  9, ‚18,20,22‘
INSERT Testdata SELECT 2,  8, ‚17,19‘
INSERT Testdata SELECT 3,  7, ‚13,19,20‘
INSERT Testdata SELECT 4,  6, “
INSERT Testdata SELECT 9, 11, ‚1,2,3,4‘
GO

The query

;WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(‚,‘, String + ‚,‘) – 1),
        STUFF(String, 1, CHARINDEX(‚,‘, String + ‚,‘), “)
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(‚,‘, String + ‚,‘) – 1),
        STUFF(String, 1, CHARINDEX(‚,‘, String + ‚,‘), “)
    FROM tmp
    WHERE
        String > “
)

SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID
— OPTION (maxrecursion 0)
— normally recursion is limited to 100. If you know you have very long
— strings, uncomment the option

Output

 SomeID | OtherID | DataItem
——–+———+———-
 1      | 9       | 18      
 1      | 9       | 20      
 1      | 9       | 22      
 2      | 8       | 17      
 2      | 8       | 19      
 3      | 7       | 13      
 3      | 7       | 19      
 3      | 7       | 20      
 4      | 6       |         
 9      | 11      | 1       
 9      | 11      | 2       
 9      | 11      | 3       
 9      | 11      | 4