sql - 如何在连接多个表时使用 GROUP BY 连接字符串？

Question

我正在连接多个表，其中我希望根据 TechnicianName 将一列值插入行中:

• 我有 4 张 table easy_tbljobcard , easy_tbltechnician和easy_tblproblem和easy_tbltechnicianMaster

• 我得到 TechnicianName在第二列中 easy_tbltechnicianMaster哪里technicianId存在于easy_tbltechnician

• 我想要STUFF在我的查询的第三列中 ( p.ProblemReported )

当前 SQL 语句:

 SELECT j.CardID, 
      , (SELECT TechnicianName FROM easy_tbltechnicianMaster WHERE TechnicianID = t.technicianID) AS TechnicianName
      , p.ProblemReported 
 FROM easy_tbljobcard AS j 
 JOIN easy_technician AS t ON t.CardID = j.CardID  
 LEFT JOIN easy_tblproblem AS p ON p.CardID = t.CardID

查询结果:

╔══════════╦══════════════════╦═══════════════════╗
║  CardID  ║  TechnicianName  ║  ProblemReported  ║
╠══════════╬══════════════════╬═══════════════════╣
║    1     ║      AKBAR       ║     PROBLEM A     ║
║    1     ║      AKBAR       ║     PROBLEM B     ║
║    1     ║      AKBAR       ║     PROBLEM C     ║
║    1     ║      ASANKA      ║     PROBLEM A     ║
║    1     ║      ASANKA      ║     PROBLEM B     ║
║    1     ║      ASANKA      ║     PROBLEM C     ║
╚══════════╩══════════════════╩═══════════════════╝

上面的结果应该转换成这样:

╔══════════╦══════════════════╦═════════════════════════════════╗
║  CardID  ║  TechnicianName  ║         ProblemReported         ║
╠══════════╬══════════════════╬═════════════════════════════════╣
║    1     ║      AKBAR       ║ PROBLEM A, PROBLEM B, PROBLEM C ║
║    1     ║      ASANKA      ║ PROBLEM A, PROBLEM B, PROBLEM C ║
╚══════════╩══════════════════╩═════════════════════════════════╝

如何在连接多个表时执行此操作？

SQLFiddle

Answer 1

您可以指定CTE – common table expression存储您的临时结果:

with cteTbl ( CardID
            , TechName
            , problemReported ) as ( 
select j.CardID
     , p.ProblemReported
     , ( select TechnicianName
         from easy_tbltechnicianMaster
         where TechnicianID =  t.technicianID ) as TechName
from easy_tbljobcard as j 
join easy_technician as t on t.CardID = j.CardID  
left join easy_tblproblem as p  on p.CardID = t.CardID )

然后从中选择，并使用将具有相同t.techName和t.CardID的所有列值连接在一行中>for xml path('') ，然后将第一个逗号 , 替换为 stuff:

select t.CardID
     , t.TechName
     , stuff( ( select ', ' + ProblemReported
                from cteTbl
                where TechName = t.TechName
                order by ProblemReported
                for xml path('') ), 1, 1, '') AS ProblemReported
from cteTbl t
group by t.TechName
       , t.CardID

SQLFiddle