Can't get my head around this

Question

Can't get my head around this

alex 29536

Grasshopper

Points: 18
More actions
May 31, 2016 at 3:59 am

#326700

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Looks like it's the concatenation part you're struggling with. Work your way through this[/url]. It looks quite daunting, but you should find something there that works for you.

John

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

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;

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

alex 29536 Grasshopper Points: 18 More actions · Answer 3

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.