none
Payroll Table Design and Query RRS feed

  • Question

  • I have the following tables for the payroll application:

    PAYROLL
    -----------------
    ID
    PeriodCovered
    Particulars

    PAYROLLDETAIL
    --------------
    ID
    PayrollID (FK => Payroll.ID)
    EmployeeID
    ItemID (FK =>  PayrollItem.ID)
    Amount


    PAYROLLITEM
    ------------------
    ID
    Item



    The reason for the payroll item table: there are several payroll columns
    that will be used for each payroll record; these columns will differ in each branch and/or period covered.

    Using T-SQL, how do I generate the following view?

    -----------------------------------------------------------------------------------------------------------------------------
    PayrollID  |  PeriodCovered  |  Particulars  |  EmployeeID  |  Item1Amount  |  Item2Amount  |  Item3Amount  |  Item4 Amount  |  ItemEtcAmount



    I have been reading the Pivot Command but it requires that the "Payroll Items" be enumerated statically.
    Please help. Thanks.
    a filipino newbie
    Friday, December 25, 2009 1:04 PM

Answers