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

Any help would be greatly appreciated.

I hope you now have learnt to avoid storing data as comma separated values. — jarlh, Feb 10 '23 at 10:05

score 2 · Accepted Answer · answered Feb 10 '23 at 08:25

2

Try this:

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

answered Feb 10 '23 at 08:25

gotqn

Thanks! I had to change compatibility level for the db, but that worked a charm. – user2007841 Feb 10 '23 at 09:58

1 Answers1