Slightly shorter version with the same problem as the one from Adam.

WITH SampleData(fullname) AS(

SELECT 'Peter Parker' UNION ALL

SELECT 'Harry Osborn' UNION ALL

SELECT 'Mary Jane Watson' UNION ALL

SELECT 'John Jonah Jameson Jr.'

)

SELECT fullname,

STUFF( fullname, 1, charindex(' ',fullname), '') + ', ' + left(fullname,charindex(' ',fullname)-1)

FROM SampleData