Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vidyar
Starting Member
2 Posts |
 Posted - 2014-03-21 : 05:59:20
|
| The database has Name,Email, and skill. Though the name is distinct it is repeated as it has different skills. I would like to remove duplicate names and add the corresponding skill to the only one row.From the stored procedure, combining 3 tables I got the output as:Name email department SkillArun emailid Tech team Technical Arun emailid Tech team LeadershipArun emailid Tech team Decision MakingBinay emailid Marketing TechnicalBinay emailid Marketing Decision MakingI would like to remove the duplicate Name fileds and combine the Skill in a single row as other fields are same. So the output should beName email department SkillArun emailid Tech team Technical, Leadership, Decision MakingBinay emailid Marketing Technical,Decision MakingPlease let me know how can I proceed? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-03-23 : 18:10:08
|
| [code]SELECT a.name, a.email, a.department, STUFF(b.departments,1,1,'') AS DepartmentsFROM (SELECT DISTINCT NAME, email, department FROM YourTable a) a CROSS APPLY ( SELECT ',' + Skill FROM YourTable b WHERE a.name = b.name AND a.email = b.email AND a.department = b.department FOR XML PATH('') ) b(departments);[/code] |
 |
|
|
vidyar
Starting Member
2 Posts |
Posted - 2014-03-26 : 01:55:16
|
| thanks James, Since i am new to SQL I was not able to update my code with the code that you have given. So i am providing all the details about my code so that you can understand my issue.The stored procedure that is giving this result is: SELECT distinct e.firstName + ' ' + e.lastName as name, e.email, e.geo,c.name as skill, d.name as department, e.addedDate, e.modifiedDate from employee e inner join mentor_offer m on e.email=m.emailinner join department don e.department = d.idinner join criteria c on m.criteria_id = c.idorder by e.addedDate descWhere Employee, mentor_offer, department and criteria are different tables.Employee has all the employee details and department has the id and name of all the departments, Criteria has id and name of all the skills.At present I get the result like this:Name|Email|Location|Department|Skill|addedDate|mod ifiedDate-----------------------------------------------------------Arun Rao|arun@test.com|NA| Marketing|Planning & Organizing| 3/17/2014 1:01:21 PM|3/20/2014 2:37:50 PMArun Rao|arun@test.com|NA| Marketing|Technical Skills| 3/17/2014 1:01:21 PM|3/20/2014 2:37:50 PMBinay|binay@test.com|NA|Sales|Leadership|3/17/2014 1:01:21 PM|3/20/2014 2:37:50 PMBinay|binay@test.com|NA|Sales|Technical Skills|3/17/2014 1:01:21 PM|3/20/2014 2:37:50 PMExpected Result: I would like to add the skills in a single row with comma separated and remove the duplicate entries of Name, Email ,Location, Department. Name|Email|Location|Department|Skill|addedDate|mod ifiedDate-----------------------------------------------------------Arun Rao|arun@test.com|NA| Marketing|Planning & Organizing, Technical Skills|3/17/2014 1:01:21 PM|3/20/2014 2:37:50 PMBinay|binay@test.com|NA|Sales|Leadership, Technical Skills|3/17/2014 1:01:21 PM|3/20/2014 2:37:50 PMPlease help. |
|
|
|
|
|
|
|
|
Need help in removing the duplicates in the table