?
Solved

how to write a Pivot table style SQL Query in this scenario

Posted on 2009-02-10
2
Medium Priority
?
336 Views
Last Modified: 2012-05-06
I  have the following tables in my database
PerformanceObjectives(PerObjID,Title,etc)
Modules(ModuleID,Title)
ObjectiveModules(PerObjID,ModuleID)
Subjects(SubjectID,Title)
ModuleSubjects(ModuleID,SubjectID)
EmployeeTrainingHours(TrainingHoursID,EmployeeID,StartDate,EndDate,SubjectID,NoOfHours)
Employee(EmployeeID,Firstname,Surname,etc).

I want my query to return the results like this

                Module1    Module2     Module3    Total
Emp1       10                5                    5           20
Emp2       5                  7                  12           24
Emp3       2                  3                   9            14

Now the Modules (Module1,Module2,Module3) values represent the sum of the hours of the subjects present in that module. e.g. Module1 may have sub1,sub2 and sub3. and the sum of hours spent on these subjects is 10. Similary each cell represent the total no of hours spent on each module. I am using SQL server 2005 and also taking some help from this post

http://www.experts-exchange.com/Programming/Languages/.NET/Q_23880880.html

but unfortunately could not figure out

 I will be very thankfull for any help.








0
Comment
Question by:mmalik15
2 Comments
 
LVL 19

Accepted Solution

by:
frankytee earned 2000 total points
ID: 23600186
assuming that there are only 3 modules:
select e.EmployeeID ,
sum(
case m.ModuleID
  when 'Module1' then e.NoOfHours
  else 0
end
) as Module1,
 
sum(
case m.ModuleID
  when 'Module2' then e.NoOfHours
  else 0
end
) as Module2,
 
sum(
case m.ModuleID
  when 'Module3' then e.NoOfHours
  else 0
end
) as Module3,
 
sum(e.NoOfHours) as Total
 
from EmployeeTrainingHours e 
inner join ModuleSubjects m
on e.SubjectID = m.SubjectID
group by e.EmployeeID

Open in new window

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23600751
This will solve your requirement:
since this is using a pivot table, your query performance will be better.
SELECT EmployeeID, ModuleID, Module1, Module2, Module3
FROM (
SELECT EM.EmployeeID, MS.ModuleID, SUM(ET.NoOfHours) TotalNoofHours
FROM Employee EM, EmployeeTrainingHours ET, ModuleSubjects MS
WHERE EM.EmployeeID = ET.EmployeeID
  AND ET.SubjectID = MS.SubjectID
GROUP BY EM.EmployeeID, MS.ModuleID) ps
PIVOT
(
max(TotalNoofHours) FOR ModuleID IN (Module1, Module2, Module3)) AS pvt

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question