A friend of mine recently asked for help with a bit of SQL he was working on. True, he was in a far away land lording it up and I could have ignored him on general principles but the way I look at it is this… If he’s out there and in work he’s not over here making a nuisance of himself (Only joking George). So I decided to help, selfless creature that I am!
He had a data-set, in this instance nurse information whereby each nurse could have multiple registrations. What he wanted to achieve was to show a distinct list of nurses with all of their registrations on one line, comma separated. I remembered a couple of approaches that he could have taken but I though that for this instance what would be the best suited is to use an undocumented feature of the FOR XML statement. So we started with the following table of nurses and their registrations:-
We then used the following SQL statement
So a brief explanation, We are using a sub select here using the XML Path with an empty nodeset (XML PATH(”). This is the hacky part as calling XML Path with an empty nodeset is not strictly supported and as such is an unintended action. The AS data() statement is required as this removes the XML tags that would otherwise be generated. As you can see the outer select retrieves distinct nurses and the sub select joins to that distinct Nurse ID. The for XML path creates a space separated list of strings which is great, this means that we can perform a trim to remove all of the trailing spaces and to comma separate them. The final thing to do, replace all embedded spaces with a comma character. Dead simple although this does rely on your data NOT having embedded spaces itself, If it does I would suggest appending two spaces to the inner select like so:-
SELECT RegNo + ' ' AS 'data()' FROM NurseRegistrations AS NR
This way the trim functions will still work and all you need to do is to change your replace to use two spaces instead of one.
My original SQL yielded the following results.
Now this solution does offend me in some ways… The fact that I have had to resort to RBAR really grates on me but today I find myself with little time and purities of perfect SQL will have to be put on hold. I am sure, though, that I can improve this given enough time to further investigate and when I do you can be assured I will update this blog
In the meantime, I hope it helps you……. George! And stop moaning about the weather!