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.
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 datatestaaabcdCurrent function returns: a, a, a, b, c, dIdeal function would return: a, b, c, d Hope this makes sense, thanks!Create FUNCTION [dbo].[fn_Test_Rollup] (@Test nvarchar(50))RETURNS varchar(8000)ASBEGIN 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 @resultEND |
|
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)" |
 |
|
qman
Constraint Violating Yak Guru
442 Posts |
Posted - 2010-12-07 : 07:40:25
|
Any help would be much appreciated, anyone out there? |
 |
|
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))RETURNSvarchar(8000)ASBEGINDECLARE @result varchar(8000)SELECT @result = coalesce(@result+';','') + TT.TESTFROM (select distinct TEST from dbo.TestTableWHERE TT.Admin_ID = @Test) AS TTRETURN 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. |
 |
|
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! |
 |
|
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. |
 |
|
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! |
 |
|
|
|
|
|
|