Solved

Combine 2 resultsets - tweak this solution

Posted on 2011-09-19
15
210 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
15 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 36560550
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
ID: 36560661
you mean replace the LAST part in that solution with what you have?
0
 
LVL 22

Expert Comment

by:pivar
ID: 36560794
Yes, sorry, I didn't see the related question.

Yes, it's the last part after the cte.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 7

Author Comment

by:Camillia
ID: 36560877
will try it and post back
0
 
LVL 7

Author Comment

by:Camillia
ID: 36562615
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
ID: 36562875
yes, this will work on 1 or more rows.
0
 
LVL 7

Author Comment

by:Camillia
ID: 36562927
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
 
LVL 7

Author Comment

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

Expert Comment

by:pivar
ID: 36563846
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
ID: 36563919
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
ID: 36564298
let me try, thanks
0
 
LVL 7

Author Comment

by:Camillia
ID: 36564369
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
ID: 36565103
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
ID: 36566468
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
ID: 36567076
Thanks so much for sticking with this. If i have other questions, i will open a related one.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Grid querry results 41 86
Downgrading MS SQL 2008 R2 Enterprise to MS SQL 2005 Standard? 12 95
tempdb log keep growing 7 56
How to create and use encrypted columns in SQL 2005? 15 64
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…

738 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