title: SQL Combining multiple rows into one row
body: <p>What I am wanting to do is combine all job codes that are associated with a VhStock.No into one row.  Either column WkRoDesc.Job_Code END AS WorkNeeded that has all job codes but the four listed below separated by a ' , ' Then combine those four job codes the same way in another column.</p>
  
  <pre><code>SELECT 
  --other things selected here
  vhstock.No AS [Tag #], 
  WkInvReg.Ro_Number
  
  CASE WHEN WkRoDesc.Job_Code = 'Outside AOR' 
           OR WkRoDesc.Job_Code = 'AOR Del'
           OR WkRoDesc.Job_Code = 'AOR L &amp; G'
           OR WkRoDesc.Job_Code = '/Cust PU/D'
      THEN NULL ELSE WkRoDesc.Job_Code END AS WorkNeeded,
    CASE WHEN WkRoDesc.Job_Code = 'Outside AOR' 
           OR WkRoDesc.Job_Code = 'AOR Del'
           OR WkRoDesc.Job_Code = 'AOR L &amp; G'
           OR WkRoDesc.Job_Code = '/Cust PU/D'
      THEN WkRoDesc.Job_Code ELSE NULL END AS DPO
  
  
  
  FROM vhstock, WkInvReg, WkRoDesc
  
  WHERE vhstock.no = WkInvReg.Stock_No AND WkInvReg.Ro_Number = WkRoDesc.Ro_Number
  </code></pre>
  
  <p>I am using a SQL server 08 R2 with a ODBC connection to query from a SQL Anywhere v.11 database.  I am using Foresight Intelligence as the interface to conduct the query.  I do not have direct access to either database as they are managed by software vendors.</p>
  
  <p>How can I do this? Have tried this but receive an error with no explanation </p>
  
  <pre><code>stuff((
      select ',' + WkRoDesc.Job_Code + ', '
      from WkRoDesc
      where WkRoDesc.Ro_Number = WkInvReg.Ro_Number
        and NOT (WkRoDesc.Job_Code = 'Outside AOR' OR WkRoDesc.Job_Code = 'AOR Del' OR WkRoDesc.Job_Code = 'AOR L &amp; G' OR WkRoDesc.Job_Code = '/Cust PU/D')
     FOR xml path('')
      ), 1, 1, '') AS WorkNeeded,    
  
  stuff((
      select ',' + WkRoDesc.Job_Code + ', '
      from WkRoDesc
      where WkRoDesc.Ro_Number = WkInvReg.Ro_Number
        and (WkRoDesc.Job_Code = 'Outside AOR' OR WkRoDesc.Job_Code = 'AOR Del' OR WkRoDesc.Job_Code = 'AOR L &amp; G' OR WkRoDesc.Job_Code = '/Cust PU/D')
     FOR xml path('')
      ), 1, 1, '') AS DPO
  </code></pre>