Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Resolved [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.

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server - put multiple rows back onto one row

    Quote Originally Posted by szlamany View Post

    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.

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  5. #5
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    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

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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
  •  



Click Here to Expand Forum to Full Width