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.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

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

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