use pubs
go

create view vwBookedCatering
as

select 754 [id], 'Coffee' [description] union all
select 754, 'Tea' union all
select 754, 'Water' union all
select 755, 'Coffee' union all
select 755, 'Tea' union all
select 755, 'Water' union all
select 756, 'Coffee' union all
select 756, 'Tea'

go

--Create a function that returns the comma seperated values for a given ID
create function dbo.BookingsById(@id int)
returns varchar(8000)
as
begin
	declare @out varchar(8000)
	select @out = coalesce(@out + ', ' + [description], [description])
	from	vwBookedCatering
	where	[id] = @id

	return @out
end
go


--Use the function
select	[id]
	,dbo.BookingsById([id]) [Bookings]
from	(select distinct [id] from vwBookedCatering) a

go
drop function dbo.BookingsById
drop view vwBookedCatering