Using distinct in a function - 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 2000 Forums

SQL Server Development (2000)

Using distinct in a function

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2006-04-05 : 08:26:14

I have a function that concatenates rows into a string. Say for example I have a column in a table called PACKID and its returns :-

URN | NAME | person_id | PACKID | PRIMARY
1 | Sample | 111111111 | 12 | 0
2 | Sample | 111111111 | 8 | 1
3 | Sample | 111111111 | 7 | 0
4 | Sample | 111111111 | 11 | 0
5 | Sample | 111111111 | 5 | 0
6 | Sample | 111111111 | 8 | 1

If I want to concatenate into a string all the packids then I have a select statement which calls my function :-

SELECT DISTINCT person_id, dbo.ConcatPackIds(person_id) as 'folder' into #temptable
FROM tblDocuments
where pack_id = 7

This select statement will return

12-8-7-11-8

However I want it to return a distinct packId so I would get

12-8-7-11

i.e. no duplicate 8, however if I simply add a distinct statement to the front of my function (below) it only returns the first packid.

My function

CREATE function dbo.ConcatPackIds(@person_id varchar(50))
returns varchar(8000)
as
begin
declare @output varchar(8000)

set @output = ''

select @output = case @output
when '' then convert(varchar(100), pack_id)
else @output + '-' + convert(varchar(100), pack_id)
end
from tblDocuments
where person_id = @person_id and [primary] = 1 and pack_id >= 7
order by pack_id

return @output
end

Can you help please

Kristen
Test

22859 Posts

Posted - 2006-04-05 : 08:41:24

This perhaps?


select @output = case @output 
when '' then convert(varchar(100), pack_id) 
else @output + '-' + convert(varchar(100), pack_id) 
end
FROM
(
SELECT DISTINCT pack_id
from tblDocuments
where person_id = @person_id and [primary] = 1 and pack_id >= 7
) AS X
order by pack_id

Kristen

jparker
Posting Yak Master

118 Posts

Posted - 2006-04-05 : 08:57:22

Cheers - spot on

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 08:59:29

Also refer
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources