CarID     CarName          Categories
    -------------------------------------------
       1       FordGT           sports, luxury
       2       Mustang          sports
       3       EscapeHybrid     SUV, hybird
       4       Expedition       SUV, luxury

set nocount on
use pubs
go

create table car (carid int identity(1,1) primary key, carname varchar(30))
create table Category (Categoryid int identity(1,1) primary key, CategoryName varchar(30))
go
create table Car_Category (CarID int references car(carid), CategoryID int references category(categoryid))
go

insert car (carname)
select 'FordGT' union all
select 'Mustang' union all
select 'EscapeHybrid'

insert Category (categoryName)
select 'sports' union all
select 'luxury' union all 
select 'SUV' union all
select 'hybrid'
go

insert Car_Category (carid, categoryid)
select 1,1 union all
select 1,2 union all
select 2,1 union all
select 2,3 union all
select 3,4
go

--first create a function to return a csv for a given car:
if object_id('dbo.CategoriesByCarID') > 0
	drop function dbo.CategoriesByCarID
go

create function dbo.CategoriesByCarID(@carid int)
returns varchar(2000)
as
begin
	declare @cats varchar(2000)

	select	@cats = coalesce(@cats + ', ' + c.CategoryName, c.CategoryName)
	from	Car_Category cc
	join	Category c
			on c.categoryid = cc.categoryid
	where	cc.carid = @carid
	order by c.categoryName

	return @cats
end
go

--now use the function
select	c.CarID
	,c.CarName
	,Categories = dbo.CategoriesByCarID(c.carid)
from	dbo.Car c

drop table Car_Category
go
drop table Car
drop table category