我将连接多个表格,想要根据TechnicianName
将一个列的值转为行:
我有4个表:
easy_tbljobcard
、easy_tbltechnician
、easy_tblproblem
和easy_tbltechnicianMaster
我从
easy_tbltechnicianMaster
中获取TechnicianName
(第二列),其中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