create table leads
(
	fname		varchar(10),
	lname		varchar(10),
	mortgage_id	int,
)

create table mortgage
(
	mortgage_lender	varchar(20),
	mortgage_id	int
)

insert into leads
select	'john', 'doe', 1 union all
select	'jane', 'doe', 2

insert into mortgage
select	'wellsfargo',  1 union all
select	'countrywide', 1 union all
select	'wamu', 2 union all
select	'wamu', 2
go

ALTER FUNCTION dbo.Concatenate( @mortgage_id integer )
	RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SET @Output = ''

	SELECT 	@Output = CASE @Output
				WHEN '' THEN mortgage_lender
				ELSE @Output + ', ' + mortgage_lender
				END
	FROM 	dbo.mortgage
	WHERE 	mortgage_id = @mortgage_id
	--group by mortgage_lender
	ORDER BY mortgage_lender

	RETURN @Output
END 
go

select	l.fname, l.lname, dbo.Concatenate(l.mortgage_id)
from	leads l inner join mortgage m
	on	l.mortgage_id	= m.mortgage_id
group by l.fname, l.lname, l.mortgage_id

drop table leads
drop table mortgage