SELECT b.STUDENT_ID,
      COURSES = 
              STUFF ( ( SELECT ','+ rtrim(a.COURSE)
						FROM STUDENT_COURSE a
						WHERE a.STUDENT_ID = b.STUDENT_ID
						FOR XML PATH(''),TYPE 
					   ).value('.','VARCHAR(MAX)') 
					  , 1,1,SPACE(0))
FROM STUDENT_COURSE b
GROUP BY b.STUDENT_ID

SELECT
	b.STUDENT_ID,
	STUFF ( ( SELECT ','+ rtrim(a.COURSE)
			FROM STUDENT_COURSE a
			WHERE a.STUDENT_ID = b.STUDENT_ID
			FOR XML PATH(''),TYPE 
		   ).value('.','VARCHAR(MAX)') 
		  , 1,1,SPACE(0))
FROM 
	(SELECT DISTINCT STUDENT_ID FROM STUDENT_COURSE) AS b

CREATE INDEX ix_student_id_inc_course ON STUDENT_COURSE(STUDENT_ID) INCLUDE(COURSE)

quote:

---

Originally posted by robvolk

Is the STUDENT_ID column indexed in STUDENT_COURSE? What does the execution plan show?

My guess is this index may speed it up:

CREATE INDEX ix_student_id_inc_course ON STUDENT_COURSE(STUDENT_ID) INCLUDE(COURSE)

---

select count(*) as a, count(distinct student_id) as b from STUDENT_COURSE

SELECT
	b.STUDENT_ID,
	STUFF(a.CoursesXML.value('.','VARCHAR(256)'),1,1,'') AS Courses
FROM 
	(SELECT DISTINCT STUDENT_ID FROM STUDENT_COURSE) AS b
	CROSS APPLY
	(
		SELECT ',' + RTRIM(a.COURSE)
		FROM STUDENT_COURSE a
		WHERE a.STUDENT_ID = b.STUDENT_ID
		FOR XML PATH(''),TYPE
	) AS a(CoursesXML);