I have the following data structure in my database:

LastName    FirstName    CourseName
John        Day          Pricing
John        Day          Marketing
John        Day          Finance
Lisa        Smith        Marketing
Lisa        Smith        Finance
etc...

The data shows employess within a business and which courses they have shown a preference to attend. The number of courses per employee will vary (i.e. as above, John has 3 courses and Lisa 2).

I need to take this data from the database and pass it to a webpage view (asp.net mvc).

I would like the data that comes out of my database to match the view as much as possible and want to transform the data using SQl so that it looks like the following:

LastName    FirstName    Course1    Course2    Course3
John        Day          Pricing    Marketing  Finance
Lisa        Smith        Marketing  Finance

Any thoughts on how this may be achieved?

Note: one of the reasons I am trying this approach is that the original data structure does not easily lend itself to be iterated over using the typical mvc syntax:

<% foreach (var item in Model.courseData) { %>

Because of the duplication of names in the orignal data I would end up with lots of conditionals in my View which I would like to avoid.

I have tried transforming the data using c# in my ViewModel but have found it tough going and feel that I could lighten the workload by leveraging SQL before I return the data.

Thanks.