Hi, I am trying to combine some rows based on employee name or employee ID and display the year to year salary all on the same line instead of in multiple row entries.
Current look:
Employee Name | EmployeeID | Year | Salary |
Joe | 101 | 2012 | 10,000 |
Tom | 202 | 2012 | 12,000 |
Joe | 101 | 2013 | 13,000 |
Tom | 202 | 2014 | 9,000 |
Tom | 202 | 2015 | 11,500 |
What I would like the output to look like:
Employee Name | EmployeeID | 2012 Salary | 2013 Salary | 2014 Salary | 2015 Salary |
Joe | 101 | 10,000 | 13,000 | - | - |
Tom | 202 | 12,000 | - | 9,000 | 11,500 |
I am still running into the issue of outputting multiple rows for the same employee name and employeeID
Any and all help would be appreciated. Thanks in advance!
Solved! Go to Solution.
It sounds like you have the Cross Tab portion figured out, but just need to consolidate salary values by employee and year first. I used your example data to first convert the salary data to a numeric field and summed by employee and year. Then a Cross Tab and Rename took care of the rest. Solution attached.