How to use GROUP BY to concatenate strings while joining multiple tables?

Question

I'm joining multiple tables in which I want one column value into row according to TechnicianName:

• I have 4 tables easy_tbljobcard , easy_tbltechnician and easy_tblproblem and easy_tbltechnicianMaster

• I am getting TechnicianName in 2nd column from easy_tbltechnicianMaster where technicianId exist in easy_tbltechnician

• I want STUFF in 3rd column in my query (p.ProblemReported)

Current SQL statement:

 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

Query result:

╔══════════╦══════════════════╦═══════════════════╗
║  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     ║
╚══════════╩══════════════════╩═══════════════════╝

The result above should be converted into this :

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

How to do this while joining multiple tables ?

SQLFiddle

Answer 1

You can specify a CTE – common table expression to store your temporary result :

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 )

And then select from it and concatenate all column values with the same t.techName and t.CardID in one row with for xml path('') and after that replace the first comma , with 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