Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Combine 2 resultsets - tweak this solution

Posted on 2011-09-19
15
Medium Priority
?
226 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

569 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