Split row value to multiple rows

Question

I have the following table in SQL Server

id	vals
1	1, 2
2	3
3	4, 5

I need a view where the values would be

id	val
1	1
1	2
2	3
3	4
3	5

I managed to split a comma and space -separated string to multiple rows like this (string to be separated is '1, 2'):

SELECT value
FROM STRING_SPLIT ( REPLACE('1, 2', ' ', ''), ',' )

However, I can't figure out how to apply this to the table so that I would get the id's to the view as well.

Answer 1

You can use the apply clause

select * 
from
  Table t
  outer apply STRING_SPLIT(t.row,',') spl