6

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

potashin
  • 44,205
  • 11
  • 83
  • 107
Siddiq Baig
  • 586
  • 2
  • 17
  • 37
  • possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) – Clockwork-Muse Jun 14 '14 at 06:04
  • @Clockwork-Muse i think it`s not duplicate.. can you plz provide me an example in which you can Group columns in one row with separator Specially while joining multiple tables as i said while joining multiple tables – Siddiq Baig Jun 14 '14 at 11:01

1 Answers1

12

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

potashin
  • 44,205
  • 11
  • 83
  • 107
  • i need this while joining multiple tables, how to do that – Siddiq Baig Jun 13 '14 at 17:09
  • @Siddiq Baig : Your question is too broad.Update your answer with your complex query, for example, specify, where do you need query with `STUFF` and so on. – potashin Jun 13 '14 at 17:14
  • @Siddiq Baig : It is hard for me now to understand what is wrong taking in consideration that the only feedback is "It is not working".Take my sql fiddle, update it with your three tables,insert necessary data and post here. – potashin Jun 13 '14 at 17:43
  • Sorry for late reply.. i have updated my question with SQL Fiddle – Siddiq Baig Jun 13 '14 at 20:42
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/55602/discussion-between-siddiq-baig-and-notulysses). – Siddiq Baig Jun 13 '14 at 21:02