Combine 2 resultsets - tweak this solution

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

you mean replace the LAST part in that solution with what you have?

Yes, sorry, I didn't see the related question.

Yes, it's the last part after the cte.

will try it and post back

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?

yes, this will work on 1 or more rows.

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

Select allOpen in new window

i have one more question ..should i close this ...but they're related

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

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.

let me try, thanks

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

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

Select allOpen in new window

Thanks so much for sticking with this. If i have other questions, i will open a related one.