STRING_SPLIT with Row Number

STRING_SPLIT is documented to not "care" about ordinal position:

The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string. You can override the final sort order by using an ORDER BY clause on the SELECT statement (ORDER BY value).

If the ordinal position of the data is important, don't use STRING_SPLIT. Personally, I recommend using delimitedsplit8k_LEAD, which includes a itemnumber column.

But idealy, the real solution is to stop storing delimited data in your database. Create 2 further tables, one with a list of the subjects, and another that creates a relationship between the student and subject.

Note that SQL Server 2022 brings a new parameter to STRING_SPLIT called ordinal which, when 1 is passed to it, will cause STRING_SPLIT to return an additional column (called ordinal) with the ordinal position of the value within the string; so you could add that column to your ORDER BY to ensure the ordering in maintained.

Of course, this doesn't change the fact that you should not be storing delimited data to start with, and should still be aiming to fix your design.

Here's an easy solution.

DECLARE @x VARCHAR(1000) = 'a,b,c,d,e,f,g';
DECLARE @t TABLE 
(
    [Index] INT PRIMARY KEY IDENTITY(1, 1)
  , [Value] VARCHAR(50)
)
INSERT INTO @t (VALUE) 
SELECT [Value] 
FROM string_split(@x, ',')

SELECT * FROM @t

Wrap it like this:

CREATE FUNCTION SPLIT_STRING2
(
  @x VARCHAR(5000)
  , @y VARCHAR(5000)
) RETURNS @t TABLE
(
  [Index] INT PRIMARY KEY IDENTITY(1, 1)
  , [Value] VARCHAR(50)
)
AS
BEGIN
    INSERT INTO @t (VALUE) 
    SELECT [Value] 
    FROM string_split(@x, @y)
    RETURN
END

Here is a recursive CTE method to parse the Subject. There's one anchor and two recursive queries. The first recursive query parses the Subjects. The second recursive query adds the summary. I added the special case of one subject. The summary and parsed subjects records are the same. (This means there was only one subjects in Subjects.) It is filtered out in this example.

This only works because a recursive CTE has only the records from the prior iteration. If looking for set N+1 and referring back to the CTE, the CTE has records from set N, not sets 1 through N. (Reminds me of the math proof - prove n = 1 is true, then prove if n true then n+1 true. Then it's true for all n > 0.)

DECLARE @delimiter char(1) = ',';

WITH part_1 as (
    -- Subjects has 0 or more "tokens" seperated by a comma
    SELECT * 
    FROM (values
        (1, 'Leo', 'Math,Science,History'),
        ('2', 'Robert', 'Gym,Math,History'),
        ('3', 'David', 'Art,Science,English,History,Computer Science'),
        ('4', 'Lazy', 'Art')
    ) t ([Row_Num],[Person],[Subjects])
), part_2 as (
    -- Anchor on the first token.  Every token has delimiter before an after, even if we have to pretend it exists
    SELECT Row_Num, Person, Subjects, 
        LEN(Subjects) + 1 as [index_max], -- the last index possible is a "pretend" index just after the end of the string
        1 as N, -- this is the first token
        0 as [index_before], -- for the first token, pretend the delimiter exists just before the first character at index 0
        CASE WHEN CHARINDEX(@delimiter, Subjects) > 0 THEN CHARINDEX(@delimiter, Subjects) -- delimiter after exists
            ELSE LEN(Subjects) + 1 -- pretend the delimiter exists just after the end of the string at index len + 1
            END as [index_after],
        CAST(1 as bit) as [is_token] -- needed to stop the 2nd recursion
    FROM part_1
    -- Recursive part that checks records for token N to add records for token N + 1 if it exists
    UNION ALL
    SELECT Row_Num, Person, Subjects, 
        index_max, 
        N + 1, 
        index_after, -- the delimiter before is just the prior token's delimiter after. 
        CASE WHEN CHARINDEX(@delimiter, Subjects, index_after + 1) > 0 THEN CHARINDEX(@delimiter, Subjects, index_after + 1) -- delimiter after exists
            ELSE index_max -- pretend the delimiter exists just after the end of the string at index len + 1
            END,
        CAST(1 as bit) as [is_token] -- needed to stop the 2nd recursion
    FROM part_2 -- a recursive CTE has only the prior result for token N, not accumulated result of tokens 1 to N
    WHERE index_after > 0 AND index_after < index_max
    UNION ALL
    -- Another recursive part that checks if the prior token is the last.  If the last, add the record with full string that was just parsed.
    SELECT Row_Num, Person, Subjects, 
        index_max, 
        N + 1, -- this is not a token
        0, -- the entire originsal string is desired
        index_max, -- the entire originsal string is desired
        CAST(0 as bit) as [is_token] -- not a token - stops this recursion
    FROM part_2 -- this has only the prior result for N, not accumulated result of 1 to N
    WHERE index_after = index_max -- the prior token was the last
        AND is_token = 1 -- it was a token - stops this recursion
        AND N > 1 -- add this to remove the added record it it's identical - 1 token
) 
SELECT Row_Num, Person, TRIM(SUBSTRING(Subjects, index_before + 1, index_after - index_before - 1)) as [token], N, 
    index_max, index_before, index_after, is_token 
FROM part_2 
ORDER BY Row_Num, N -- Row_Num, is_token DESC, N is not required

Row_Num     Person token                                        N           index_max   index_before index_after is_token
----------- ------ -------------------------------------------- ----------- ----------- ------------ ----------- --------
1           Leo    Math                                         1           21          0            5           1
1           Leo    Science                                      2           21          5            13          1
1           Leo    History                                      3           21          13           21          1
1           Leo    Math,Science,History                         4           21          0            21          0
2           Robert Gym                                          1           17          0            4           1
2           Robert Math                                         2           17          4            9           1
2           Robert History                                      3           17          9            17          1
2           Robert Gym,Math,History                             4           17          0            17          0
3           David  Art                                          1           45          0            4           1
3           David  Science                                      2           45          4            12          1
3           David  English                                      3           45          12           20          1
3           David  History                                      4           45          20           28          1
3           David  Computer Science                             5           45          28           45          1
3           David  Art,Science,English,History,Computer Science 6           45          0            45          0
4           Lazy   Art                                          1           4           0            4           1