Unpivot/flatten from a comma delimited column

Question

I have a SQL table with two columns

Location	Sites
L1	Sa,Sb,Sc,Sd
L2	Sa,Sb,Sx

I would like a query to flatten this to

Location	Site
L1	Sa
L1	Sb
L1	Sc
L1	Sd
L2	Sa
L2	Sb
L2	Sx

Any help would be greatly appreciated.

Answer 1

Try this:

SELECT Location
      ,[value] AS Site
FROM mytable
CROSS APPLY STRING_SPLIT(Sites, ',')