title: sql server split comma separated values into columns
body: <p>I trying to split the csv to individual columns </p>
  
  <p><strong>SAMPLE DATA</strong></p>
  
  <pre><code>PAR_COLUMN  PERIOD  VALUE       mul_query
  ----------  ------  ---------   ---------
  1           601     10.134542   10.134542
  1           602     20.234234   10.134542*20.234234
  1           603     30.675643   10.134542*20.234234*30.675643
  1           604     40.234234   10.134542*20.234234*30.675643*40.234234
  2           601     10.345072   10.345072
  2           602     20.345072   10.345072*20.345072
  2           603     30.345072   10.345072*20.345072*30.345072
  2           604     40.345072   10.345072*20.345072*30.345072*40.345072
  </code></pre>
  
  <p><strong>EXPECTED RESULT :</strong></p>
  
  <pre><code>PAR_COLUMN  period   value     (No column name)    (No column name)    (No column name)    (No column name)
  ----------  ------  ---------  ----------------    ----------------    ----------------     ---------------
  1           601     10.134542  10.134542            1                   1                   1
  1           602     20.234234  10.134542            20.234234           1                   1
  1           603     30.675643  10.134542            20.234234           30.675643           1
  1           604     40.234234  10.134542            20.234234           30.675643           40.234234
  2           601     10.345072  10.345072            1                   1                   1
  2           602     20.345072  10.345072            20.345072           1                   1
  2           603     30.345072  10.345072            20.345072           30.345072           1
  2           604     40.345072  10.345072            20.345072           30.345072           40.345072
  </code></pre>
  
  <p>I tried like this. It is working but very slow when data is large. Is there any better alternative. </p>
  
  <pre><code>declare @sql varchar(max) = ''
  set @sql =
  ';WITH Split_Names 
  AS
  (
      SELECT PAR_COLUMN,
      mul_query,period,
      CONVERT(XML,''&lt;Names&gt;&lt;name&gt;''  
      + REPLACE(mul_query,''*'', ''&lt;/name&gt;&lt;name&gt;'') + ''&lt;/name&gt;&lt;/Names&gt;'') AS xmlname
        FROM #finals
  )
  SELECT PAR_COLUMN,
      period,
  '
  
  
  declare @start int =1 ,@count int 
  set @count = (select (max(period) - min(period))+1 from #finals)
  while @start &lt;= @count
  begin 
  set @sql +=concat( 'isnull(xmlname.value(''/Names[1]/name[',@start,']'',''float''),1) , ')
   set @start+=1
  end
  set @sql =left(@sql,len(@sql)-1)
  set @sql+= ' FROM Split_Names'
  
  exec( @sql)
  </code></pre>
  
  <p><strong>Note:</strong> The question is <strong>NOT</strong> to convert <code>CSV</code> to Individual <code>Rows</code>. I am trying to convert <code>CSV</code> to indivdual <code>Columns</code>  Basically am trying to calculate <em>RUNNING Multiplication</em> in <code>Value</code> column</p>