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

[MMock]

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.

[szlamany]

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.

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

[MMock]

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!"

[MMock]

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).

[wild_bill]

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

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'

[MMock]

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.