how to get a timetable like layout using c#

Hi Experts,
I have few tables in SQL Server 2005
Category(CatID,Desc,Status)
Subject(SubID,Desc,Status,CatID)
Employee(EmpID,Firstname,Surname,etc).
EmployeeSubjectHours(EmpID,SubID,NoOfHours)
I want to create a from with the layout added in a code snippet. A category is selected then display no of hours an employee has been trained on a particular subject.  How can i acheive this kind of layout using asp.net. I am using vs 2005 with SQL Server 2005.
I will appreciate any help either ideas or examples



Select Category	Dropdownlist
 
	         Sub1	Sub2     Sub3     Total
Employee 1	5.00	4.00	3.00	12.00
Employee 2	8.00	9.00	2.00	19.00
Employee 3	4.00	4.50	3.50	12.00

Open in new window

mmalik15Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tigin44Commented:

You can obtain  alist like you want by using a pivot query like the one below...


SELECT E.EmpID,E.Firsname,E.lastname,
      SUM(CASE ES.SubID WHEN 1 THEN ES.NoOfHours ELSE 0 END) AS Sub1,
      SUM(CASE ES.SubID WHEN 2 THEN ES.NoOfHours ELSE 0 END) AS Sub2,
      SUM(CASE ES.SubID WHEN 3 THEN ES.NoOfHours ELSE 0 END) AS Sub3,
      SUM(CASE ES.SubID WHEN 4 THEN ES.NoOfHours ELSE 0 END) AS Sub4,
      SUM(CASE ES.SubID WHEN 5 THEN ES.NoOfHours ELSE 0 END) AS Sub5,
      SUM(ES.NoOfHours) AS Tolat


FROM  Category C
      INNER JOIN Subject S ON C.CatID = S.CatID
      INNER JOIN EmployeeSubjectHours ES ON S.SubID = ES.SubID
      INNER JOIN Employee E ON ES.EmpID = E.EmpID
GROUP BY E.EmpID,E.Firsname,E.lastname
0
mmalik15Author Commented:
Excellent tigin44 you are  a SQL genius. I really appreciate your comment.
There are little things needed to be corrected. First on above query we are iterating on five subjects but subjects can be many and we have to calculate no of subjects at run time, how can set the no of subjects at runtime.
Secondly i tried to get Subject titles rather sub1 or sub2. I tried to add the titles like
SUM(CASE ES.SubID WHEN 1 THEN ES.NoOfHours ELSE 0 END) AS ES.SubTitle, but it gives me an error how can we get around this error.
Thanks again






0
tigin44Commented:


First of all you can not give the column name as colum alias as you have written...

Second.. if number of subject changes then you can use a dymanic query to produce the result set e.g.

CURSOR cSubject  FOR AS
   SELECT SubID, Desc
   FROM Subject

DECLARE @SubID   int,  @Desc   varchar(100)
DECLARE @sql   varchar(8000)

SET @sql   = 'SELECT E.EmpID,E.Firsname,E.lastname'

OPEN cSubject  
FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
WHILE(@@FETCH_STATUS <> 0)
BEGIN
    SET @sql   = @sql  + ', '
   SET @sql   = @sql  + ' SUM(CASE ES.'+ SubID +' WHEN 1 THEN ES.NoOfHours ELSE 0 END) AS ' +  @Desc

   FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
END
CLOSE cSubject  
DEALLOCATE cSubject  

SET @sql   = @sql  + ', SUM(ES.NoOfHours) AS Total '+
     'FROM  Category C '+
      'INNER JOIN Subject S ON C.CatID = S.CatID '+
      'INNER JOIN EmployeeSubjectHours ES ON S.SubID = ES.SubID '
      'INNER JOIN Employee E ON ES.EmpID = E.EmpID '+
   'GROUP BY E.EmpID,E.Firsname,E.lastname '

EXEC (@sql)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

mmalik15Author Commented:
Thanks sir,

For some reasons its not executing the cursor part

I have m ade some changes to table names and field names  and it looks like this.

Also i have printed @sql and it gives me query like this in snippet

SELECT E.EmployeeID,E.Firstname,E.lastname, SUM(ES.NoOfHours) AS Total FROM  tblGradlAcctTrainingCategories C INNER JOIN dbo.tblGradAcctTrainingSubject S ON C.CatID = S.CatID INNER JOIN tblGradAcctTrainingHours ES ON S.SubjectID = ES.SubjectID INNER JOIN dbo.tblEmployee E ON ES.EmployeeID = E.EmployeeID GROUP BY E.EmployeeID,E.Firstname,E.lastname

It does not give me the subject details at all.







DECLARE cSubject CURSOR
   For SELECT SubjectID, Subject
   FROM tblGradAcctTrainingSubject
 
DECLARE @SubID   int,  
@Desc   varchar(100)
DECLARE @sql   varchar(8000)
 
SET @sql   = 'SELECT E.EmployeeID,E.Firstname,E.lastname'
 
OPEN cSubject  
FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
WHILE(@@FETCH_STATUS <> 0)
BEGIN
   SET @sql   = @sql  + ', '
   SET @sql   = @sql  + ' SUM(CASE ES.'+ @SubID +' WHEN 1 THEN ES.NoOfHours ELSE 0 END) AS ' +  @Desc
 
   FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
END
CLOSE cSubject  
DEALLOCATE cSubject  
 
SET @sql   = @sql  + ', SUM(ES.NoOfHours) AS Total '+
     'FROM  tblGradlAcctTrainingCategories C '+
      'INNER JOIN dbo.tblGradAcctTrainingSubject S ON C.CatID = S.CatID '+
      'INNER JOIN tblGradAcctTrainingHours ES ON S.SubjectID = ES.SubjectID ' +
      'INNER JOIN dbo.tblEmployee E ON ES.EmployeeID = E.EmployeeID '+
      'GROUP BY E.EmployeeID,E.Firstname,E.lastname '
--print @sql
 
EXEC (@sql)

Open in new window

0
mmalik15Author Commented:
I have solved one problem but the there is a syntax error i am getting now

Syntax error converting the varchar value 'SELECT E.EmployeeID,E.Firstname,E.lastname, SUM(CASE ' to a column of data type int.


DECLARE cSubject CURSOR
   For SELECT SubjectID, Subject
   FROM tblGradAcctTrainingSubject
 
 
DECLARE @SubID   int,  
@Desc   varchar(100)
DECLARE @sql   varchar(8000)
 
SET @sql   = 'SELECT E.EmployeeID,E.Firstname,E.lastname'
 
OPEN cSubject  
FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
WHILE(@@FETCH_STATUS = 0)
BEGIN
   SET @sql   = @sql  + ', '
   SET @sql   = @sql  + ' SUM(CASE ES.'+ @SubID +' WHEN 1 THEN ES.NoOfHours ELSE 0 END) AS ' +  @Desc
 
   FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
END
CLOSE cSubject  
DEALLOCATE cSubject  
 
SET @sql   = @sql  + ', SUM(ES.NoOfHours) AS Total '+
     'FROM  tblGradlAcctTrainingCategories C '+
      'INNER JOIN dbo.tblGradAcctTrainingSubject S ON C.CatID = S.CatID '+
      'INNER JOIN tblGradAcctTrainingHours ES ON S.SubjectID = ES.SubjectID ' +
      'INNER JOIN dbo.tblEmployee E ON ES.EmployeeID = E.EmployeeID '+
      'GROUP BY E.EmployeeID,E.Firstname,E.lastname '
--print @sql
 
EXEC (@sql)

Open in new window

0
mmalik15Author Commented:
Great
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.