ASP.NET Pivot Table: How to use it with just two tables in the database

Question

I have an Excel sheet that list all the employees in the company with some required training courses. The list is very big and long and I need to incorporate it within the company website. therefore, I am thinking to use the pivot table with the stored procedures in order to make the table flexible for expanding with adding new employees or courses in the future.

The main problem now is how to use it with just two tables in the database which are Employee table and Courses Table.

Employee table consists of: employee name, id, organization, course id Courses table consists of: course name, course id

I want a pivot table that lists employee name on the first column and lists courses on the first row. then it will show me (yes or no) values under each course for each employee which indicates that employee takes this course or not. Finally I want to see a total of yes on the last row of the table

I know the syntax of the pivot table and I tried to understand it and make it work for this case but I failed.

I am using this valuable resource: http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx

How to use it with this case? Any hint please? I just wanna know the structure of the query

My initial query is:

 select
  *
from
(
  select
    employee.Name, employee.id, employee.Organization, courses.id, courses.name
  from employee, courses

) DataTable
PIVOT
(
  SUM(ID)
  FOR Name
  IN (
    [safety awareness],[general safety orientation],[sms orientation],[emergency responses]
  )
) PivotTable

Answer 1

I would definitely use a PivotGrid control like DevXpress has for winforms and ASP.NET.

With such control you can create pivots at design time and even allow end user to drag and drop fields around at runtime and decide for the pivoting logic than save their preferences. Used this for some advanced reporting tools and users loved it.