Post reply

Need Help with Pivot / Unpivot SQL

  • June 17, 2021 at 6:58 pm

    #3897643

    Hi

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

     

    Issue

  • June 18, 2021 at 3:37 pm

    #3898045

    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!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

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

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