I have a dataset with ID, names and year. Each ID has multiple records with different names and their corresponding year of joining. I want to transpose data based on ID and the year of joining.
Each row should have unique ID
Name column will be split into Name1, Name2, Name3 and so on
Name1 column should contain date with earliest year of joining followed by Name2 and then Name3
For ID as 1 the name1 column contains John as John has the earliest joining date for ID as 1
| Input | | | |
| ID | Name | Year | |
| 1 | Steve | 2008 | |
| 1 | Neha | 2006 | |
| 1 | John | 2005 | |
| 2 | Nisha | 2012 | |
| 2 | Natasha | 2021 | |
| 2 | Natila | 2000 | |
| 3 | Richard | 2014 | |
| 3 | Rishabh | 2013 | |
| | | | |
| Output | | | |
| ID | Name1 | Name2 | Name3 |
| 1 | John | Neha | Steve |
| 2 | Natalia | Nisha | Natasha |
| 3 | Rishabh | Richard | |
If there is more than 1 Name for an ID then additional names will be transposed to another filed based on the date of joining.
Much appreciate your help.
Best
Pooja
