set nocount on
use northwind

--------------------------------------------------------------------
--DDL/DML
create table Names (ID int, [First Name] varchar(20), Surname varchar(20))
create table AKA (ID int, NamesID int, AKAFirstName varchar(20), AKASurname varchar(20))
go
insert Names 
select 1, 'John', 'Murphy' union all
select 2, 'Mary', 'Jones' union all
select 3, 'Julian', 'Duff'

insert AKA
select 1, 2, 'May', null union all
select 2, 2, 'Beth', null union all
select 3, 2, 'Jane', null union all
select 4, 3, 'Jules', 'Duffey' union all
select 5, 3, 'JJ', null
--------------------------------------------------------------------
go
--Function to return a comma seperated list of AKAs for a given NameID
create function dbo.fn_AKA(@namesid int, @FS char(1) )
returns varchar(200)
as
begin
	declare @aka varchar(200)
	if @FS = 'F'
	begin
		select @aka = coalesce(@aka + ', ' + AKAFirstName, AKAFirstName)
		from	AKA
		where	NamesID = @namesid
		and	AKAFirstName is NOT NULL
	end
	else
	begin
		select @aka = coalesce(@aka + ', ' + AKAsurname, AKAsurname)
		from	AKA
		where	NamesID = @namesid
		and	AKASurname is NOT NULL
	end

	return @aka
end
go
--------------------------------------------------------------------
--select statement using function
select	FName = [First Name] + coalesce(' (' + dbo.fn_AKA(ID,'F') + ')', '')
	,Surname = Surname + coalesce(' (' + dbo.fn_AKA(ID,'S') + ')', '')
from	Names

go

--------------------------------------------------------------------
--cleanup
drop function dbo.fn_AKA
drop table AKA
drop table Names