[RESOLVED] SQL Server - put multiple rows back onto one row

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.

Basically you create a UDF for that - even though doing I/O in a UDF is frowned upon this is one time that it really makes sense

Here is an example - getting nurse visit details for a student.

Code:

---

Create Function dbo.GetVisitDetail_F(@VisitId int)
Returns varchar(5000)
As
Begin
        Declare @RS varchar(5000)

        Select @RS=IsNull(@RS+', ','')+VD.Category
                From VisitDetail_T VD
                Where VD.VisitId=@VisitId

        Return @RS
End

---

Basically using a SELECT to BUILD A VARIABLE - expecting several rows to be fed into it - so the IsNull use is kind of key to this whole operation.

When do I start getting a commission for this ;)

Quote:

---

Originally Posted by szlamany

When do I start getting a commission for this ;)

---

Before I even read this thread, but after I saw you posted, I was thinking to myself, "I will have to give Steve my paycheck for this week!"

Anyway, I will have to digest that a bit before I am ready to tackle it. So I will say thanks now but I may want to ask a question about it later (as in tomorrow).

I've seen this done with XML. I had to do some searching, but this might work.

Code:

---

declare @hcp table(provid int, specialty varchar(50), description char(20))

insert into @hcp(provid,specialty,description) values
(1,'Cardiovascular Disease', 'Primary'),
(1,'Internal Medicine ','Other'),
(1,'General Practice','Other')

select p.provid, specialty as 'Primary',o.OtherSpecialty
from @hcp p
Inner join (
        select distinct t2.provid,
                (Select t1.specialty + ' | ' AS [text()]
                From @hcp t1
                where t1.provid = t2.provid and description <> 'Primary'
                order by t1.provid
                for xml path ('')) [OtherSpecialty]
        from @hcp t2) o on p.provid = o.provid
where p.description = 'Primary'

---

szlamany - That is so beast! And I have everything done in time to report at our daily 9:00 a.m. meeting. Thanks so much!

wild bill - Thanks for the alternative. I tried szlamany's first simply because it was posted first, and it solved my problem perfectly.