将多行数据合并到同一列

Question

我想创建一个工作ID和列的列表，列出所有匹配的不同工作类别，但连接到同一列。作为一个例子，现在工作82041有两个类别，但现在返回两行。我希望它能够在一个领域内说“零售，销售助理”。

我试过的代码如下，有人可以告诉我我做错了什么吗？

例：

jobOrderID (No column name) 

82027 Motion Graphics    
82029 Other       
82030 Product Designer    
82041 Retail       
82041 Sales Associate    
82069 Social Media  

示例代码：

select JobOrder.jobOrderID ,  
     stuff((select distinct ', ' + cast(Category.occupation as nchar(30)) 
       from Category 
       where Category.categoryID = JobOrderCategories.categoryID 
     for xml path ('')),1,1,'')  

from JobOrder 
left outer join JobOrderCategories 
    on JobOrder.joborderid = JobOrderCategories.jobOrderID 
left outer join Category 
    on Category.categoryID = JobOrderCategories.categoryID 

where JobOrder.dateAdded > '2017-5-2' and JobOrder.dateAdded < '2017-5-3' 
    and joborder.isDeleted = 0 

order by joborder.dateAdded asc 

Answer 1

想通了通过改变左侧加入

select 

JobOrder.jobOrderID, 

stuff((select distinct ', ' + cast(Category.occupation as varchar(30)) 
from Category 
left outer join JobOrderCategories on (Category.categoryID = JobOrderCategories.categoryID and joborder.jobOrderID = JobOrderCategories.joborderid) 
where Category.categoryID = JobOrderCategories.categoryID 
for xml path ('')),1,1,'') 


from JobOrder 

where JobOrder.dateAdded > '2017-5-2' and JobOrder.dateAdded < '2017-5-3' 
and joborder.isDeleted = 0