concatenating multiple values using Joins

Question

I have 3 tables as following

JobPost_Master1_UT

JobPost_ID  JobPost_Name
-----------  -----------
1            .Net Developer
2            Java Developer

Skillset_Master_UT

Skill_ID     SkillName 
-----------  -----------
1            Javascript
2            Bootstrap
3            HTML
4            .Net MVC
5            JSP

JobPost_Skill

JobPost_ID  Skill_ID     
-----------  -----------
1             1
1             2
1             3
2             3
2             5

I want this result

JobPost_ID   SkillName
-----------  -----------
1            Javascript,Bootstrap,HTML
2            HTML,JSP

I am trying this

DECLARE @SkillName VARCHAR(1000)
SELECT @SkillName = COALESCE(@SkillName ,'') + SkillName + ',' 
FROM JobPost_Skill
     left outer join Skillset_Master_UT
          on Skillset_Master_UT.Skill_ID =JobPost_Skill.Skillset_ID
     left outer join JobPost_Master1_UT
          on JobPost_Skill.JobPost_ID =JobPost_Master1_UT.JobPost_ID
group by JobPost_Master1_UT.JobPost_ID, @SkillName

But it gives me following result

SkillName
-----------
1  Javascript,Bootstrap,HTML,HTML,JSP

Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — James Z, Feb 19 '17 at 20:25

score 0 · Accepted Answer · answered Feb 19 '17 at 20:37

0

You have to use a query like the following

SELECT JobPost_ID , STUFF((SELECT ',' + T1.Skillname
                           FROM  Skillset_Master_UT T1 INNER JOIN 
                                  JobPost_Skill T2 ON T1.Skill_ID = T2.Skill_ID
                            WHERE T2.JobPost_ID = TBL.JobPost_ID
                            FOR XML PATH('')),1,1,'') as Skillname
FROM JobPost_Master1_UT TBL

answered Feb 19 '17 at 20:37

Hadi

36,233
13
65
124

@Ash if it working fine. You have to [accept the answer](http://stackoverflow.com/tour) so the question is marked as answered – Hadi Feb 20 '17 at 06:58

concatenating multiple values using Joins

1 Answers1