HELP - Combining Rows in a View

Hi All,

I can do this in Access, with VB, but I'm pretty new to SQL Server.

Say you have the following table, call it TblStudents:

Grade Name
8 John
8 Mike
8 Ed
9 Tom
9 Greg
10 Jack
10 Tony

And you wanted a view that would give you:

Grade Name
8 John, Mike, Ed
9 Tom, Greg
10 Jack, Tony

How would you do this in SQL Server?

Thanks.

HenryRemember, you asked! I'd use:CREATE TABLE tHenry (
grade INT
, name VARCHAR(20)
)

INSERT INTO tHenry (grade, name)
SELECT 8, 'John'
UNION ALL SELECT 8, 'Mike'
UNION ALL SELECT 8, 'Ed'
UNION ALL SELECT 9, 'Tom'
UNION ALL SELECT 9, 'Greg'
UNION ALL SELECT 10, 'Jack'
UNION ALL SELECT 10, 'Tony'
GO

CREATE FUNCTION dbo.fHenry(@.piGrade INT) RETURNS VARCHAR(200) AS
BEGIN
DECLARE @.cList VARCHAR(8000)

SELECT @.cList = Coalesce(@.cList + ', ' + name, name)
FROM tHenry
WHERE grade = @.piGrade

RETURN @.cList
END
GO

CREATE VIEW vHenry AS SELECT DISTINCT TOP 100 PERCENT
grade, dbo.fHenry(grade) AS students
FROM tHenry
ORDER BY grade
GO

SELECT * FROM vHenry
GO

DROP VIEW vHenry
DROP FUNCTION dbo.fHenry
DROP TABLE tHenry-PatP