STRING_SPLIT with Row Number

Question

Here is what I am trying to produce:

Row_Num Person                  Value   Row_Number
  1        Leo                   Math   1
  1        Leo                Science   2
  1        Leo                History   3
  1        Leo   Math,Science,History   4
  2     Robert                    Gym   2
  2     Robert                   Math   3
  2     Robert                History   4
  2     Robert       Gym,Math,History   1
  3      David                    Art   1
  3      David                Science   2
  3      David               English    3
  3      David               History    4
  3      David       Computer Science   5
  3      David  Art,Science,English,History,Computer Science    6

This is the code I am using:

with part_1 as
(
    select
        1 as [Row_Num],
        'Leo' as [Person],
        'Math,Science,History' as [Subjects]
    ---
    union
    ---
    select
        '2',
        'Robert',
        'Gym,Math,History'
    ---
    union
    ---
    select
        '3',
        'David',
        'Art,Science,English,History,Computer Science'
    ---
)
----------------------------------------------------------------------
select
    [Row_Num],
    [Person],
    [Subjects]
into
    #part1
from
    part_1;
go
--------------------------------------------------------------------------------
with p_2 as(

    select
        [Row_Num],
        [Person],
        --[Subjects],
        [value]

    from
        #part1
    cross apply
        STRING_SPLIT([Subjects],',')

    union all

    select
        [Row_Num],
        [Person],
        [Subjects]
    from
        #part1
    

)

select
     [Row_Num]
    ,[Person]
    ,[Value]
    ,row_number()
        over(Partition by Row_Num order by (select 1)) as [Row_Number]
from
    p_2
order by
     [Row_Num]
    ,[Row_Number]

Here is what I am producing:

Row_Num Person  Value   Row_Number
1   Leo Math    1
1   Leo Science 2
1   Leo History 3
1   Leo Math,Science,History    4
2   Robert  Gym,Math,History    1
2   Robert  Gym 2
2   Robert  Math    3
2   Robert  History 4
3   David   Art 1
3   David   Science 2
3   David   English 3
3   David   History 4
3   David   Computer Science    5
3   David   Art,Science,English,History,Computer Science    6

It looks good, until you look at Robert. All of the subjects are on the first row, instead of the bottom.

Any suggestions?

Answer 1

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.

Answer 2

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