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

Posted on 2009-02-10
Last Modified: 2012-05-06
I  have the following tables in my database

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

but unfortunately could not figure out

 I will be very thankfull for any help.

Question by:mmalik15
    LVL 19

    Accepted Solution

    assuming that there are only 3 modules:
    select e.EmployeeID ,
    case m.ModuleID
      when 'Module1' then e.NoOfHours
      else 0
    ) as Module1,
    case m.ModuleID
      when 'Module2' then e.NoOfHours
      else 0
    ) as Module2,
    case m.ModuleID
      when 'Module3' then e.NoOfHours
      else 0
    ) 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

    LVL 57

    Expert Comment

    by:Raja Jegan R
    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
    max(TotalNoofHours) FOR ModuleID IN (Module1, Module2, Module3)) AS pvt

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how the fundamental information of how to create a table.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now