Assuming you're using SQL Server 2005:
This should do what you're after - obviously replace fields as you need:
For demo purposes, consider the following two table structures:
Students(
STU_PKEY Int Identity(1,1) Constraint PK_Students_StuPKey Primary Key,
STU_NAME nvarchar(64)
)
Courses(
CRS_PKEY Int Identity(1, 1) Constraint PK_Courses_CrsPKey Primary Key,
STU_KEY Int Constraint FK_Students_StuPKey Foreign Key References Students(STU_PKEY),
CRS_NAME nvarchar(64)
)
Now this query should do the job you're after:
Select s.STU_PKEY, s.STU_NAME As Student,
Stuff((
Select ',' + c.CRS_NAME
From Courses c
Where s.STU_PKEY = c.STU_KEY
For XML Path('')
), 1, 1, '') As Courses
From Students s
Group By s.STU_PKEY, s.STU_NAME
Way simpler than the currently accepted answer...