T-SQL STUFF, STRING_SPLIT

In cases where you must extract a series of values from a delimited string, you can utilize SQL Server's built-in string_split() function. You need only specify the delimited string and the delimiting character (commas in this case ",") as the arguments.

When a requirement calls for you to roll up data into a single row, you can use STUFF() (with or without combined with FOR XML, depending on your data). Just specify an inner query as the argument for STUFF().

 --Get ids from a csv of ids  
 SELECT * FROM string_split('100,101,201,301,411,414', ',')  
 

--Push a series of values into one row  
 SELECT playerID,  
 STUFF((select '; ' + teamID  
      from Managers  
      where teamID like('%P%')  
            group by teamID  
      for xml path('')), 1, 1, '') allTeams  
 FROM Managers  

Result of these functions