none
Looking to combine Horizonta data with vertical data

    Question

  • Hi I have 2 tables one has roles for project like

    Staffing Plan

    role id, role type.. few other columns and then for each role ID I have weeks and hours for each week like

    RoleID  WeekNumber Hours

    Now I need to get output as:

    Role id Role type .... and then Week1 hours, week2 hours..

    Role id Role type .... and then Week1 hours, week2 hours..

    reards

    varun sharma

    Sunday, October 27, 2013 12:45 AM

Answers

  • Something like below

    SELECT RoleID,RoleType, [1] AS Week1Hours, [2] AS Week2hours, [3] AS Week3Hours,

    [4] AS Week4Hours,

    .. FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY RoleID ORDER BY WeekNumber) AS Seq,r.RoleID,r.RoleType,Hours FROM RolesTable r INNER JOIN StaffingPlan s ON s.RoleID = r.RoleID )t PIVOT (SUM(Hours) FOR Seq IN ([1],[2],[3],[4],..))p




    Sunday, October 27, 2013 8:18 AM