If you wouldn't mind going to this thread for an explanation of what tables I am selecting from in my query. I have another question which has nothing to do with the UNION statement as that thread's title implies so I'm beginning a new thread (with a really bad title).

I'm selecting an HCP (health care provider). He can have one primary specialty and multiple other specialties. So I have to go to tblHCPSpecialties with his ID. And I can get back, for example, a row saying his primary specialty is "Cardiovascular Disease" and two more rows with other specialties as "Internal Medicine" and "General Practice". What I want to do is return this in one row, with a column called PrimarySpecialties and value "Cardiovascular Disease" and a column called OtherSpecialties and value "Internal Medicine | General Practice". I hate to just say "how do I do that" but the complexity of this query is growing beyond my expertise. So thanks for any help.