Improve company productivity with a Business Account.Sign Up

x
?
Solved

Combine 2 resultsets - tweak this solution

Posted on 2011-09-19
15
Medium Priority
?
229 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
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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 2000 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

606 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