TSQL STUFF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

TSQL STUFF

Author

Topic

byka
Starting Member

18 Posts

Posted - 2014-06-06 : 08:20:59

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-06 : 09:04:01

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

byka
Starting Member

18 Posts

Posted - 2014-06-06 : 09:13:25

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-06 : 11:30:16

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources