Need unique value of a column while combining two tables

Question

I'm getting two columns from two different tables using sql server 2005.. For that I used the following query..

SELECT [MHD].JobPostingId,
       [C].CandidateName 
  FROM 
       Candidate as C RIGHT JOIN MemberHiringDetails MHD 
                      ON [MHD].MemberId=[C].Id

It returns the following data:

JobPostingId  CandidateName
60            aaa
60            bbb
63            aaa

NULL means, that candidate is erased from that table.. But I need output as follows:

JobPostingId  CandidateName
60            aaa, bbb
63            aaa

How to do this..? If it need stored procedure means, ok.. All I need is solution..

score 0 · Answer 1 · edited May 23 '17 at 12:03

Agreed with Mahmoud Gamal, duplicate. Extracted from another post and changed for your tables:

SELECT C.name,
       STUFF((SELECT ','+ C.CandidateName
               FROM Candidate C
              WHERE C.Id = MHD.MemberId
            FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
 FROM MemberHiringDetails MHD

thx to OMG Ponies in post: How do I Create a Comma-Separated List using a SQL Query?

Need unique value of a column while combining two tables

1 Answers1