sql server programming: Preventing duplicates in a coalesce query -- sql server programming

Preventing duplicates in a coalesce query

Daniel Badger
9/16/2007 6:30:00 PM

sql server programming:

I have the following query running in a stored procedure -

SELECT @UnitString = COALESCE(@UnitString + ',', '') + '3_' +
ltrim(str(Id)) + '_' + ltrim(str(ChildId))
FROM #Members
WHERE (Lineage LIKE '%/1/'+ ltrim(str(@MemberId)) +'/'+
ltrim(str(@ParentId)) +'%/')

If for example the result returns "3_55_122, 3_55_123, 3_55_122,3_55_156"

How could I prevent duplicates ('3_55_122') being picked up in the query
more than once so that it only returns "3_55_122, 3_55_123, 3_55_156"?

Re: Preventing duplicates in a coalesce query

Daniel Badger
9/16/2007 9:40:01 PM

What I mentioned is correct - it is only a snippet of a rather large stored
procudure.

The "3_55_122, 3_55_123, 3_55_122,3_55_156" values are inserted it a
database field once the stored procedure is run.

I just want to cut the duplicates out when I do the selection as I have
mentioned in previous post.

[quoted text, click to view]

Re: Preventing duplicates in a coalesce query

Shiju Samuel
9/16/2007 11:15:27 PM

Group by should eliminate duplicate

SELECT @UnitString = COALESCE(@UnitString + ',', '')
+ '3_' + ltrim(str(Id)) + '_' + ltrim(str(ChildId))
FROM #Members
WHERE (Lineage LIKE '%/1/'+ ltrim(str(@MemberId)) +'/'+
ltrim(str(@ParentId)) +'%/')
group by id,childid

Hope it helps
-
Shiju

Re: Preventing duplicates in a coalesce query

Anith Sen
9/17/2007 12:00:00 AM

You are using a suspect syntax ( aggregate concatenation via select ) that
is unpredicatable.

If you cannot use a client side tool to generate this resultset, consider
one of the options detailed at: www.projectdmx.com/tsql/rowconcatenate.aspx

--
Anith

Re: Preventing duplicates in a coalesce query

--CELKO--
9/17/2007 4:24:26 AM

Please post some DDL. It looks like your are trying to violate 1NF
and build a CSV string in SQL. That would be totally absurd, of
course; so what did you mean to do?

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.