your DROP TABLE statement has an extra space, here's the corrected version, and I added in one in the 2008 & After section if you wanted to order by lastname instead of firstname (didn't modify the others because I'm stuck in 2014 at the moment) :

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;

CREATE TABLE #TestData (
fk_id INT NOT NULL IDENTITY(1,1),
FullNameCSV VARCHAR(8000) NOT NULL
);
INSERT #TestData (FullNameCSV) VALUES
('Tamesha Rios, Damian Richard, Tim David'),
('Jeff Moden, Thom A, PSB, Jason Long');

--================================================================================================

--------------------------------------------------------------------------------------------------
-- SS 2008 & after
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + LTRIM(ds.Item)
FROM
dbo.DelimitedSplit8K(td.FullNameCSV, ',') ds --<< http://www.sqlservercentral.com/articles/Tally+Table/72993/
ORDER BY
LTRIM(ds.Item)
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;

--------------------------------------------------------------------------------------------------
-- SS 2008 & after - Lastname first
SELECT
    td.fk_id,
    FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
    #TestData td
    CROSS APPLY (
                SELECT
                    ', ' + substring(LTRIM(ds.Item),charindex(' ',LTRIM(ds.Item))+1,len(ds.Item)) + ', '+ substring(Ltrim(ds.Item),1,charindex(' ',Ltrim(ds.Item)))
                FROM
                    dbo.DelimitedSplit8K(td.FullNameCSV, ',') ds    --<< http://www.sqlservercentral.com/articles/Tally+Table/72993/
                ORDER BY
                    --LTRIM(ds.Item)
     ', ' + substring(LTRIM(ds.Item),charindex(' ',LTRIM(ds.Item))+1,len(ds.Item)) + ', '+ substring(Ltrim(ds.Item),1,charindex(' ',Ltrim(ds.Item)))
                FOR XML PATH('')
                ) fco (FullNameCSV_Ordered)
GROUP BY
    td.fk_id,
    fco.FullNameCSV_Ordered;
--------------------------------------------------------------------------------------------------
-- SS 2016 & after
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + LTRIM(ss.value)
FROM
STRING_SPLIT(td.FullNameCSV, ',') ss --<< STRING_SPLIT() became available in 2016
ORDER BY
LTRIM(ss.value)
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;

--------------------------------------------------------------------------------------------------
-- SS 2017
SELECT
td.fk_id,
FullNameCSV_Ordered = STRING_AGG(LTRIM(ss.value), ', ') WITHIN GROUP (ORDER BY LTRIM(ss.value)) --<< STRING_AGG() became available in 2017
FROM
#TestData td
CROSS APPLY STRING_SPLIT(td.FullNameCSV, ',') ss
GROUP BY
td.fk_id;