title: Efficient way to string split using CTE
body: <p>I have a table that looks like</p>
  
  <pre><code>ID  Layout
  1   hello,world,welcome,to,tsql
  2   welcome,to,stackoverflow
  </code></pre>
  
  <p>The desired output should be</p>
  
  <pre><code>Id  Splitdata
  1   hello
  1   world
  1   welcome
  1   to
  1   tsql
  2   welcome
  2   to
  2   stackoverflow
  </code></pre>
  
  <p>I have done this by the below query</p>
  
  <pre><code>Declare @t TABLE(
      ID  INT IDENTITY PRIMARY KEY,
      Layout VARCHAR(MAX)
  )
  INSERT INTO @t(Layout)
  SELECT 'hello,world,welcome,to,tsql' union all
  SELECT 'welcome,to,stackoverflow'
  --SELECT * FROM @t
  ;With cte AS(
  select F1.id
   ,O.splitdata 
   from
   (
   select *,
   cast('&lt;X&gt;'+replace(F.Layout,',','&lt;/X&gt;&lt;X&gt;')+'&lt;/X&gt;' as XML) as xmlfilter
   from @t F
   )F1
   cross apply
   ( 
   select fdata.D.value('.','varchar(MAX)') as splitdata 
   from f1.xmlfilter.nodes('X') as fdata(D)) O
   )
  
   select * from cte
  </code></pre>
  
  <p>But performance wise it is very bad. I am looking for a more efficient query but using CTE only. </p>