Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Combine 2 resultsets - tweak this solution

Posted on 2011-09-19
15
Medium Priority
?
224 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

963 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