CODEWITHSUNDEEP
c#sqlasp.net
mcclosa
mcclosa

Reputation: 1455

Preventing duplication from SQL Query, ASP.net

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)

SQL Query Builder Results

Upvotes: 1

Views: 88

Answers (3)

Tuan Le PN
Tuan Le PN

Reputation: 384

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.

Upvotes: 0

Mike
Mike

Reputation: 3811

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

Upvotes: 1

User2012384
User2012384

Reputation: 4919

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.

Upvotes: 1

Related Questions