Preventing duplication from SQL Query, ASP.net

Question

I have the below SQL query using the Query Builder in Visual Studio. As you can see the same user is duplicated 3 times, this is due to the user having 3 different skills. How can I merge the 3 skills together in the SQL query or in a ListView control so that it only displays one result instead of 3 and that the user has their 3 skills listed?

SELECT users.role_id, users.username, users.first_name, users.last_name, users.description, roles.role_id, roles.role, skills.skill_id, skills.user_id, skills.skill 
FROM users 
INNER JOIN roles ON users.role_id = roles.role_id 
INNER JOIN skills ON users.user_id = skills.user_id 
WHERE (users.role_id = 3)

How would you like to display the data? do you want to join the three results together with comma seperator or you can display in listview in frontend. — User2012384
– User2012384, Commented Oct 30, 2015 at 2:30
I would like to display in listview on the frontend so that the user is only listed once, but each skill is attributed to them. So having: Username: developertest, Skill: Skill1, Skill2, Skill3. Currently in listiview it's listed as Username: developertest, Skill: Skill1 | Username: developertest, Skill: Skill2 — mcclosa
– mcclosa, Commented Oct 30, 2015 at 2:35

Mike · Accepted Answer · 2015-10-30 02:57:50Z

1

Use For XML Path(''), Type. It is a bit of a hack, because you're really creating an XML string without a root and fashioning odd elements, but it works well. Be sure to include the Type bit, otherwise the XML trick will attempt to convert special characters, like < and & into their XML escape sequences (here is an example).

Here is a simplified version of your problem in a SQL Fiddle. Below is the relevant Select snippet.

SELECT users.user_id, users.first_name, 
STUFF(
  (SELECT  ', ' + skill
        FROM skills
        WHERE users.user_id = skills.user_id 
        FOR XML PATH(''), TYPE
         ).value('.', 'VARCHAR(MAX)')
  , 1, 2, '') AS skill_list
FROM users

answered Oct 30, 2015 at 2:57

Mike

3,8513 gold badges31 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

User2012384 · Accepted Answer · 2015-10-30 02:39:58Z

1

Try using Stuff and For Xml

Here's the Fiddle:

http://sqlfiddle.com/#!6/fcf71/5

See if it helps, it's just a sample so you will have to change the column names.

answered Oct 30, 2015 at 2:39

User2012384

4,92717 gold badges74 silver badges107 bronze badges

Comments

Tuan Le PN · Accepted Answer · 2015-10-30 05:11:49Z

You can use PIVOT on the Skill then group those skills into one column. To make it simple, I test it with some sample data like the following:

CREATE SCHEMA _Test

CREATE TABLE _Test.SkillSet(SkillId INT IDENTITY(1,1) PRIMARY KEY, SkillName NVARCHAR(64))
INSERT INTO _Test.SkillSet(SkillName) VALUES('C/C++')
INSERT INTO _Test.SkillSet(SkillName) VALUES('C#')
INSERT INTO _Test.SkillSet(SkillName) VALUES('Java')

CREATE TABLE _Test.Employees(EmpId INT IDENTITY(1,1) PRIMARY KEY, FullName NVARCHAR(256))

INSERT INTO _Test.Employees(FullName) VALUES('Philip Hatt')
INSERT INTO _Test.Employees(FullName) VALUES('John Rosh')

CREATE TABLE _Test.Employee_Skill(EmpId INT FOREIGN KEY REFERENCES _Test.Employees(EmpId), SkillId INT FOREIGN KEY REFERENCES _Test.SkillSet(SkillId))

INSERT INTO _Test.Employee_Skill(EmpId, SkillId) VALUES(1, 1)
INSERT INTO _Test.Employee_Skill(EmpId, SkillId) VALUES(1, 2)
INSERT INTO _Test.Employee_Skill(EmpId, SkillId) VALUES(1, 3)

INSERT INTO _Test.Employee_Skill(EmpId, SkillId) VALUES(2, 2)
INSERT INTO _Test.Employee_Skill(EmpId, SkillId) VALUES(2, 3)

WITH tEmpSkill
AS
(SELECT A.EmpId, A.FullName, C.SkillName
FROM _Test.SkillSet C RIGHT JOIN
(
    _Test.Employees A LEFT JOIN _Test.Employee_Skill B
    ON A.EmpId = B.EmpId
)
ON B.SkillId = C.SkillId
)
SELECT * FROM tEmpSkill
PIVOT(COUNT(SkillName) FOR SkillName IN([C/C++], [C#], [Java])) AS Competency

The query above gives me an intermediate result PIVOT RESULT

Now you can easily make a string containing all the skills needed for each employee. You can also search for some articles to use the PIVOT with unknown number of columns (skill sets), which may better serve your need. Hope this can help.

Collectives™ on Stack Overflow

Preventing duplication from SQL Query, ASP.net

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related