Subquery split comma separated value into two columns
<p>I have a field that sometimes has one value and sometimes 2 values separated by a comma.I need to split the values into two columns. I have seen solutions using functions, but i need to add it to a view.</p>

<p>Here is what I have been trying to do for the first column</p>

<pre><code>SELECT 
CASE
WHEN EXISTS(SELECT reportto FROM dbo.placement WHERE reportto like '%,%') 
THEN
(Select Substring(reportTo, 1,Charindex(',', reportTo)-1) as [Primary OTS Approver]
from placement) 
ELSE (SELECT reportto as [Primary OTS Approver] FROM dbo.placement)
END
</code></pre>