SQL Combining multiple rows into one row

Question

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.

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 & 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 & 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

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.

How can I do this? Have tried this but receive an error with no explanation

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 & 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 & G' OR WkRoDesc.Job_Code = '/Cust PU/D')
   FOR xml path('')
    ), 1, 1, '') AS DPO

Check this [post](http://stackoverflow.com/questions/180032/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-sql-server-2005). You need to use the `Stuff` function with `Path Xml for` — Nilesh, Sep 16 '13 at 13:37
Just tried to do that with the code above and it throws an error but within gui it doesn't say what the error was. — Troy, Sep 16 '13 at 13:59
It doesn't have an exact error just says failed to validate the database SQL Statement — Troy, Sep 16 '13 at 14:46

Angelo · Answer 1 · 2013-09-17T07:02:13.980

0

Hi have you tried groupby? hope this helps

MAX(DISTINCT CASE WHEN WkRoDesc.Job_Code = 'Outside AOR' THEN WkRoDesc.Job_Code END) + ',' + MAX(DISTINCT CASE WHEN WkRoDesc.Job_Code ='AOR Del' THEN WkRoDesc.Job_Code END) 
                  + ',' + MAX(DISTINCT CASE WHEN WkRoDesc.Job_Code ='AOR L & G' THEN WkRoDesc.Job_Code END) + ',' + MAX(DISTINCT CASE WHEN WkRoDesc.Job_Code ='/Cust PU/D' THEN WkRoDesc.Job_Code END) 
                  AS WorkNeeded

the result is WorkNeeded = Outside AOR,AOR Del,AOR L & G,/Cust PU/D

edited Sep 17 '13 at 07:02

answered Sep 17 '13 at 02:32

Angelo

335
4
10

Doesn't work SQL verification failed. I'm guessing SQL anywhere is throwing the error as this is my first time using it. And this makes sense to me – Troy Sep 17 '13 at 14:35

SQL Combining multiple rows into one row

1 Answers1