在查询中组合两个表

Question

我在SQL 2008数据库中有两个表，当我运行查询时需要合并两列。

表1我需要Emply_ID，Emply_Name，Emply_HomeDept，Emply_Shift

表2我需要Emply_ID，Emply_OtherDept，Emply_AltShift。

我倒是要能够做的是生产单排，结合HomeDept和OtherDept一起Shift和AltShift在一起。所以，我最终会与

00124|John Doe|Loading,Packing,Shipping|Shift1,Shift2,Shift3 

Answer 1

这应该符合您的期望：

SELECT t.EmplyId, 
     t.Emply_name, 
     CONCAT(t.Emply_HomeDept, ',' , x.otherDept) AS FullDept, 
     CONCAT(t.Emply_Shift, ',' , x.AltShift) AS FullShift, 
FROM 
    Table1 t 
    LEFT JOIN (
    SELECT t2.EmplyId, 
      (SELECT t3.OtherDept + ',' AS [text()] 
      FROM table2 t3 
      WHERE t3.EmplyId = t2.EmplyId 
      FOR XML PATH ('') 
      ) AS otherDept, 
      (SELECT t4.AltShift + ',' AS [text()] 
      FROM table2 t4 
      WHERE t4.EmplyId = t2.EmplyId 
      FOR XML PATH ('') 
      ) AS AltShift   
    FROM 
     Table2 t2) x 
    ON t.Emply_Id = x.EmplyId