How can I 'transpose' my data using SQL and remove duplicates at the same time?
- by Remnant
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.
