May 31, 2016 at 3:59 am
Hi all, looking for some help.
I've only ever done basic select statements and joins before and am really struggling with this. I've tried playing with STUFF, nested queries, cross apply etc. but cant seem to crack it. This is what I'm trying to achieve
Table Employees
Column StaffNumber
Column Surname
e.g.
StaffNumberSurname
635723Smith
123536Jones
567547Terry
388345MacLeod
Table B
Column StaffNumber
Column QualificationDescription
e.g.
StaffNumberQualificationDescription
635723Maths
635723English
635723Science
123536French
567547English
388345Science
388345Design
From these two tables I'm trying to output a table which has just a single line per Employee, with the qualifications concatenated in a single column. e.g
StaffNumber Surname Qualifications
635723 Smith Maths, English, Science
123536 Jones French
567547 Terry English
388345 MacLeod Science, Design
I then want to join this data to ANOTHER table which shares a common Staff Number
Thanks in advance
May 31, 2016 at 4:21 am
May 31, 2016 at 6:51 am
Something like this. The below mentioned link has a good explanation on the method used.
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
SELECT E.StaffNumber,
STUFF( (
SELECT ',' + Emp.QualificationDescription
FROM Employees AS Emp
WHERE Emp.StaffNumber = E.StaffNumber
ORDER BY Emp.QualificationDescription
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)')
,1,1,'') AS Qualifications
FROM Employees AS E
GROUP BY E.StaffNumber;
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 31, 2016 at 9:00 am
Thank you both very much for your help, I've managed to crack it now - the code is very similar to what I wrote, but wasn't quite there with the logic. Cheers.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply