title: Sybase IQ - Splitting comma separated values in a column into rows
body: <p>I see other solutions on here regarding splitting data values into rows but so far none of them work on Sybase IQ - either because it's not supported or I don't have the proper access to create procedures.  I can do this very easily in Java but trying to do it in a query instead so I can avoid the overhead.</p>
  
  <p>So what I'm trying to do is take this...</p>
  
  <pre><code> ID     | Data
   abc    | 18,20,22
   def    | 17,19
   ghi    | 13,19
  </code></pre>
  
  <p>And convert it to...</p>
  
  <pre><code> ID     | Data
   abc    |  18
   abc    |  20
   abc    |  22
   def    |  17
   def    |  19
   ghi    |  13
   ghi    |  19
  </code></pre>
  
  <ol>
  <li><p>I tried the recursive method but got an error that the remote server does not support it</p></li>
  <li><p>I tried the XML version below but I keep getting a syntax error on the last line.  The only thing I can think of is that Cross Apply isn't supported since Aqua Data isn't highlighting the word as it does with everything else.</p>
  
  <pre><code> SELECT A.ID,  
        Split.a.value('.', 'VARCHAR(100)') AS Data  
   FROM  
   (
   SELECT ID,  
       CAST ('&lt;M&gt;' + REPLACE(Data, ',', '&lt;/M&gt;&lt;M&gt;') + '&lt;/M&gt;' AS XML) AS Data  
   FROM  mytable
   ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a); 
  </code></pre></li>
  <li><p>I'm unable to create functions / procedures because of permissions</p></li>
  <li><p>I tried using this Sybase function (sa-split-list)...</p>
  
  <p><a href="http://dcx.sybase.com/1200/en/dbreference/sa-split-list-sysproc.html" rel="nofollow">http://dcx.sybase.com/1200/en/dbreference/sa-split-list-sysproc.html</a></p>
  
  <p>But not sure how to incorporate it</p></li>
  </ol>
  
  <p>Anyone able to help?</p>