SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 TSQL STUFF
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

byka
Starting Member

18 Posts

Posted - 06/06/2014 :  08:20:59  Show Profile  Reply with Quote
I have the following query where I am using STUFF to generate string. How can I check if InNetCoInsurance values are same don't add to string? I need to add only different values.

SELECT RegionName,BenefitTypeDesc,FilingDesc, DraftingNote,AdminNote,Combine,
STUFF((SELECT '; ' + InNetCoInsurance
FROM @Benefits US
WHERE US.BenefitTypeID = us.BenefitTypeID
FOR XML PATH('')), 1, 1, '') [InNetCoInsurance]
FROM @Benefits WHERE Combine='True'
GROUP By RegionName,BenefitTypeDesc,FilingDesc, DraftingNote,AdminNote,Combine
ORDER BY 1

byka

James K
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 06/06/2014 :  09:04:01  Show Profile  Reply with Quote
Add a distinct clause as shown below.
quote:
Originally posted by byka

I have the following query where I am using STUFF to generate string. How can I check if InNetCoInsurance values are same don't add to string? I need to add only different values.

SELECT RegionName,BenefitTypeDesc,FilingDesc, DraftingNote,AdminNote,Combine,
STUFF((SELECT DISTINCT '; ' + InNetCoInsurance
FROM @Benefits US
WHERE US.BenefitTypeID = us.BenefitTypeID
FOR XML PATH('')), 1, 1, '') [InNetCoInsurance]
FROM @Benefits WHERE Combine='True'
GROUP By RegionName,BenefitTypeDesc,FilingDesc, DraftingNote,AdminNote,Combine
ORDER BY 1

byka

Go to Top of Page

byka
Starting Member

18 Posts

Posted - 06/06/2014 :  09:13:25  Show Profile  Reply with Quote
Thank you, it worked.
Another question:I need to compare 3 columns values in a row and if values are different leave row if values in 3 columns are the same roll up into 1 row?

STUFF((SELECT DISTINCT '; ' + InNetBenefitMax
FROM @Benefits US
WHERE US.BenefitTypeID = us.BenefitTypeID
FOR XML PATH('')), 1, 1, '') InNetBenefitMax,

STUFF((SELECT DISTINCT '; ' + OutNetCoInsurance
FROM @Benefits US
WHERE US.BenefitTypeID = us.BenefitTypeID
FOR XML PATH('')), 1, 1, '') OutNetCoInsurance,

STUFF((SELECT DISTINCT '; ' + OutNetBenefitMax
FROM @Benefits US
WHERE US.BenefitTypeID = us.BenefitTypeID
FOR XML PATH('')), 1, 1, '') OutNetBenefitMax,


byka
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 06/06/2014 :  11:30:16  Show Profile  Reply with Quote
quote:
Originally posted by byka

Thank you, it worked.
Another question:I need to compare 3 columns values in a row and if values are different leave row if values in 3 columns are the same roll up into 1 row?

STUFF((SELECT DISTINCT '; ' + InNetBenefitMax
FROM @Benefits US
WHERE US.BenefitTypeID = us.BenefitTypeID
FOR XML PATH('')), 1, 1, '') InNetBenefitMax,

STUFF((SELECT DISTINCT '; ' + OutNetCoInsurance
FROM @Benefits US
WHERE US.BenefitTypeID = us.BenefitTypeID
FOR XML PATH('')), 1, 1, '') OutNetCoInsurance,

STUFF((SELECT DISTINCT '; ' + OutNetBenefitMax
FROM @Benefits US
WHERE US.BenefitTypeID = us.BenefitTypeID
FOR XML PATH('')), 1, 1, '') OutNetBenefitMax,


byka

I did not understand the question.

This is what I understood:
1. You have a table with 3 columns (and may be other columns).
2. For each row in this table, if the values in these 3 rows are the same, do nothing at all.
3. If the values in the 3 columns in a given row happen to be different, then do something. You said you want to "roll up into one row". What does that mean? Can you post an example?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000