Help with a matrix or pivot table?

I'm trying to create a table that is a combination of two tables, and the number of columns is dynamic. So I have 2 tables, Students and Assignments. I'd like to get a result with the students on the left and the assignments across the top. I'm not sure where to start, any help would be great. Thanks

What your describing is not (1) a table, (2) a view nor (3) a table valued function because none of these can contain a dynamic number of columns. Where you need to start is by describing more clearly your needs.

If you are using SQL Server 2005 you probably use the ROW_NUMBER() function and PIVOT to slot different classes into ordinally assigned columns. This might be done with either a view or a TVF; however, if you truly need a dynamic number of columns, first rethink this at least a little. Once you decide you need a dynamic number of columns, you will need to settle with dynamic SQL.

|||

I'll try to be more clear. I need to display a table that has a row for each student. For each student row I'd like to have a column for each assignment completed. The tables look like this. The number of assignments could be 0 to 100, so the number of columns is dynamic.

tblStudent

StudentId

Name

tblAssignment

AssignmentId

StudentId

Score

Example

Assignment 1 | Assignment 2 | Assignment 3

Student X 10 20 30

Student Y 5 15 40