Most succinct way to transform a CSV string to an table in TSQL?

See my answer from here

But basically you would:

Create this function in your DB:

CREATE FUNCTION dbo.Split(@origString varchar(max), @Delimiter char(1))     
returns @temptable TABLE (items varchar(max))     
as     
begin     
    declare @idx int     
    declare @split varchar(max)     

    select @idx = 1     
        if len(@origString )<1 or @origString is null  return     

    while @idx!= 0     
    begin     
        set @idx = charindex(@Delimiter,@origString)     
        if @idx!=0     
            set @split= left(@origString,@idx - 1)     
        else     
            set @split= @origString

        if(len(@split)>0)
            insert into @temptable(Items) values(@split)     

        set @origString= right(@origString,len(@origString) - @idx)     
        if len(@origString) = 0 break     
    end 
return     
end

and then call the function and pass in the string you want to split.

Select * From dbo.Split(@roles, ',')

If you're working with SQL Server compatibility level 130 then the STRING_SPLIT function is now the most succinct method available.

Reference link: https://msdn.microsoft.com/en-gb/library/mt684588.aspx

Usage:

SELECT * FROM string_split('Pub,RegUser,ServiceAdmin',',')

RESULT:

value
-----------
Pub
RegUser
ServiceAdmin

Here's a thorough discussion of your options:

• Arrays and Lists in SQL Server