Need Help with Pivot / Unpivot SQL

  • Hi

    Can any one help me with query to transfer from position1 to position2 as shown below ?

     

    Issue

  • One way to do it that I think should work would be to do a self join.  So something like:

    SELECT
    [t1].[empID]
    , [t1].[empl_rcd]
    , [t1].[jobcode] AS [jobcode1]
    , [t1].[joblevel] AS [joblevel1]
    , [t1].[payperiod] AS [payperiod1]
    , [t1].[salary] AS [salary1]
    , [t2].[jobcode] AS [jobcode2]
    , [t2].[joblevel] AS [joblevel2]
    , [t2].[salary] AS [salary2]
    FROM[dbo].
    AS [t1]
    JOIN[dbo].
    AS [t2]
    ON [t1].[empID] = [t2].[empID]
    AND [t1].[emprcd] = [t2].[emprcd]
    AND [t2].[payperiod] = 2
    WHERE[t1].[payperiod] = 1;

    NOTE - I have no data to test if this works, I am just going off of my gut feeling.  Plus, this will ONLY work with payperiod being either 1 or 2.  As soon as you have more pay periods you want to work with, the code will fail.  I do not see a way to pivot the data you are asking about using the PIVOT or UNPIVOT syntax, but maybe another expert will have a better solution.

    If you can provide some sample data and DDL, it would make it easier to build up some solutions.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply