-
Feb 22nd, 2012, 02:03 PM
#1
Thread Starter
PowerPoster
[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.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Feb 22nd, 2012, 02:33 PM
#2
Re: SQL Server - put multiple rows back onto one row
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
-
Feb 22nd, 2012, 02:56 PM
#3
Thread Starter
PowerPoster
Re: SQL Server - put multiple rows back onto one row
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!"
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Feb 22nd, 2012, 02:58 PM
#4
Thread Starter
PowerPoster
Re: SQL Server - put multiple rows back onto one row
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).
Last edited by MMock; Feb 22nd, 2012 at 02:59 PM.
Reason: fixed sentence
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Feb 22nd, 2012, 04:33 PM
#5
Re: SQL Server - put multiple rows back onto one row
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'
That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma
Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney
-
Feb 23rd, 2012, 09:17 AM
#6
Thread Starter
PowerPoster
Re: SQL Server - put multiple rows back onto one row
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.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|