quick DISTINCT / COALESCE question

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 2000 Forums

SQL Server Development (2000)

quick DISTINCT / COALESCE question

Author

Topic

petem
Starting Member

44 Posts

Posted - 2004-10-21 : 11:15:11

Hi,

I have the following function in SQL Server:
-------------------------------------------------------
alter Function getIDusedSearch (@ID varChar)
RETURNS varChar(1000)
AS
BEGIN
Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + accessory
FROM table1 WHERE ID= @ID
return @List
END
-------------------------------------------------------
This returns a comma delimited list of all the accessories that have that ID.

Unfortunately some IDs have duplicated information.
ie: the same accessory may be listed twice for an ID.

How can I only return only DISTINCT values to perform the COALESCE function on?

Thanks,

Pete

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 11:19:12

alter Function getIDusedSearch (@ID varChar)
RETURNS varChar(1000)
AS
BEGIN
Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + accessory
FROM
(SELECT DISTINCT accessory
FROM table1 WHERE ID= @ID
) X
return @List
END
Kristen

petem
Starting Member

44 Posts

Posted - 2004-10-21 : 11:30:54

Cheers Kristen,

For some reason, though, I'm still getting the duplicate returned...

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 11:55:48

Try this then:

SELECT DISTINCT DATALENGTH(accessory), '[' + accessory + ']'
FROM table1 WHERE ID= 1234

perhaps you have a space or something weird in the accessory column?

Kristen

petem
Starting Member

44 Posts

Posted - 2004-10-21 : 12:12:14

The SELECT DISTINCT sub query works fine on it's own - it's when I try to use it with the COALESCE that it decides it wants to return everything.

BTW: thanks for the DATALENGTH function - very useful :)
I check and the columns are exactly alike.

Pete

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 13:12:38

Let me make 100% sure I've got this:

SELECT DISTINCT accessory FROM table1 WHERE ID = 1234

returns a nice little list of "accessory" data

but

Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + accessory
FROM
(SELECT DISTINCT accessory
FROM table1 WHERE ID= 1234
) X
SELECT @List

has a comma delimited list, but it contains duplicate values?

I'm gonna have to eat my hat ... I really am ... :-(

accessory doesn't have values like
1
2
3
1,2,3
4
does it? <vbg>

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources