Solved

Combine 2 resultsets - tweak this solution

Posted on 2011-09-19
15
173 Views
Last Modified: 2012-05-12
The solution in the related question works. But I want to combine the rows that have the same BusinessNameId.
In the example I provided , i get 3 rows back but i need to get 2 rows because 2 rows have BusinessNameId=3

Having comma or dash in between is fine. I'll separate it in the code while binding to teh gridview..

HCprovider      BusinessNameId
Bob Jones              3
officer 1 llll             39
pp333 ppp              3

----  I need it to be displayed like this:

Bob Jones , pp333 pp             3
officer 1 llll                              39
0
Comment
Question by:Camillia
  • 8
  • 6
15 Comments
 
LVL 22

Expert Comment

by:pivar
Comment Utility
Hi,

Try using FOR XML PATH, which should look something like

SELECT BusinessNameId, STUFF((SELECT ', '+t2.HCprovider AS[text()] FROM table1 t2 WHERE t2.BusinessNameId =t1.BusinessNameId FOR XML PATH('')), 1, 2, '') AS list
FROM table1 t1
GROUP BY t1.BusinessNameId

/peter
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
you mean replace the LAST part in that solution with what you have?
0
 
LVL 22

Expert Comment

by:pivar
Comment Utility
Yes, sorry, I didn't see the related question.

Yes, it's the last part after the cte.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
will try it and post back
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
it worked. Question; in my example, I had 2 rows that i wanted to combine. This method you posted woll work if i have more than 2 repeated rows...correct?
0
 
LVL 22

Expert Comment

by:pivar
Comment Utility
yes, this will work on 1 or more rows.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
just came across something (was about to open another question)..i actually need to bring back these columns because i bind it to a gridview in code;

HCProvider, -- (this one is covered by that STUFF statement in sql)
hcid,
BusinessNameID, --(this one is also covered in the sql you have)
businessName

I changed the solution to below and I used your code. I tried adding "hcid" and "businessname" but the colum "list" comes out as one long column...

This is what I have now but need to add 2 more columns:
insert into @offices
select distinct 
HCProvider, 
hcid,
BusinessNameID,
businessName
from offices o
 inner join dbo.SignupDescription sc on o.businessnameId = sc.Id

SELECT BusinessNameId,
 STUFF((SELECT ', '+t2.HCprovider AS[text()]  
 --,hcid,businessname
FROM @offices t2 
WHERE t2.BusinessNameId =t1.BusinessNameId FOR XML PATH('')), 1, 2, '') AS list
FROM @offices t1
GROUP BY t1.BusinessNameId

Open in new window

0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 7

Author Comment

by:Camillia
Comment Utility
i have one more question ..should i close this ...but they're related
0
 
LVL 22

Expert Comment

by:pivar
Comment Utility
try, if business is related and hcid another id

SELECT BusinessNameId, hcid,max(businessname),
 STUFF((SELECT ', '+t2.HCprovider AS[text()]  
FROM @offices t2
WHERE t2.BusinessNameId =t1.BusinessNameId FOR XML PATH('')), 1, 2, '') AS list
FROM @offices t1
GROUP BY t1.BusinessNameId, t1.hcid
0
 
LVL 22

Expert Comment

by:pivar
Comment Utility
as for the other q, if it`s related to the above solution post it here. otherwise I think you should open a new q.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
let me try, thanks
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
Yes, this works but one last question. I realized  I need to combine "hcid" column as well. I tried the "stuff" code for hcid but get an error...

The result is now like this

3      1      Test Business33      Bob Jones, pp333 ppp
3      11      Test Business33      Bob Jones, pp333 ppp
39      12      Username test      officer 1 llll

But I need it to be like this (because the first 2 rows have businessnameid=3...name is combined but hcid column also needs to be combined and have a comma in between). How can I do this?

3      1,11      Test Business33      Bob Jones, pp333 ppp
39      12      Username test      officer 1 llll
0
 
LVL 22

Accepted Solution

by:
pivar earned 500 total points
Comment Utility
Try

SELECT BusinessNameId, max(businessname),
STUFF((SELECT ', '+t2.HCprovider AS[text()] FROM @offices t2 WHERE t2.BusinessNameId =t1.BusinessNameId FOR XML PATH('')), 1, 2, '') AS HCproviders
STUFF((SELECT ', '+CONVERT(VARCHAR(10), t2.hcid) AS[text()] FROM @offices t2 WHERE t2.BusinessNameId =t1.BusinessNameId FOR XML PATH('')), 1, 2, '') AS hcids
FROM @offices t1
GROUP BY t1.BusinessNameId


0
 
LVL 21

Expert Comment

by:Alpesh Patel
Comment Utility
Create SQL Function to combine the values of same id.

CREATE FUNCTION [dbo].[CombineValues] (
@ID Varchar(50),
@FromDAte Date,
@ToDate Date
)
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @ReasonCodeList VARCHAR(MAX);
    SELECT @ReasonCodeList = COALESCE(@ReasonCodeList + ', ', '') + 
        CAST(Reason AS varchar(500))
    FROM 
		(Select 
					Distinct Reason 
				From
		(Select ID, ContestationReasonCode From ContestedMembers 
		Where FileDAte BETWEEN @FromDAte AND @ToDate AND ) C 
		INNER JOIN  Contestation D 
		ON C.ContestationReasonCode=D.ReasonCode
		) D
    --print @ReasonCodeList
RETURN @ReasonCodeList
END

Open in new window

0
 
LVL 7

Author Closing Comment

by:Camillia
Comment Utility
Thanks so much for sticking with this. If i have other questions, i will open a related one.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now