tsql - Most succinct way to transform a CSV string to a table in T-SQL?

Question

-- Given a CSV string like this:

declare @roles varchar(800)
select  @roles = 'Pub,RegUser,ServiceAdmin'

-- Question: How to get roles into a table view like this:

select  'Pub'
union
select  'RegUser'
union
select  'ServiceAdmin'

---

After posting this, I started playing with some dynamic SQL. This seems to work, but seems like there might be some security risks by using dynamic SQL - thoughts on this?

declare @rolesSql varchar(800)
select  @rolesSql = 'select ''' + replace(@roles, ',', ''' union select ''') + ''''
exec(@rolesSql)

Answer 1

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