Need Help with Pivot / Unpivot SQL

• kvnreddyece

  Valued Member

  Points: 62

  More actions

  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 ?

   

  

• Mr. Brian Gale

  SSC-Insane

  Points: 24690

  More actions

  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.