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 2005 Forums
 Transact-SQL (2005)
 Distinct values function help

Author  Topic 

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-12-06 : 14:14:54
I have a function (see below) which strings together test values as a single column. Some of these values returned contain duplicates. How can I strip out the duplicate values, only showing unique values.

Sample data

test
a
a
a
b
c
d

Current function returns: a, a, a, b, c, d
Ideal function would return: a, b, c, d

Hope this makes sense, thanks!

Create FUNCTION [dbo].[fn_Test_Rollup] (@Test nvarchar(50))
RETURNS
varchar(8000)
AS
BEGIN
DECLARE
@result varchar(8000)
SELECT @result = ''
SELECT
@result = @result + ACT.TEST + ';'
FROM dbo.TestTable AS TT
WHERE (TT.Admin_ID = @Test)
IF len(@result )>0
SELECT distinct @result = left(@result, len(@result)-1)
RETURN
@result
END

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-12-06 : 14:36:44
Minor typo in my line that says "SELECT distinct @result = left(@result, len(@result)-1)".

It should read "SELECT @result = left(@result, len(@result)-1)"
Go to Top of Page

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-12-07 : 07:40:25
Any help would be much appreciated, anyone out there?
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 07:59:09
Also ACT should be TT.

Create FUNCTION [dbo].[fn_Test_Rollup] (@Test nvarchar(50))
RETURNS
varchar(8000)
AS
BEGIN
DECLARE @result varchar(8000)
SELECT @result = coalesce(@result+';','') + TT.TEST
FROM (select distinct TEST from dbo.TestTable
WHERE TT.Admin_ID = @Test) AS TT
RETURN coalesce(@result,'')
END

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-12-07 : 08:16:41
Nigelrivett, you rule the school, that worked quite nicely!

I had to add some minor formatting to the return in order to strip the preceding ";"

substring(coalesce(@result,'') , 2 , len(coalesce(@result,'') ) - 1)

Again, thanks!
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 08:28:51
Shouldn't have needed that.
Did you add the SELECT @result = '' back in?
If you remove it the result should be correct.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-12-07 : 09:04:03
Oops, you are right... When I implemented your solution, the select was left in place. It's working fine once I removed it.

Thanks!
Go to Top of Page
   

- Advertisement -