Moving Row-Level Output data into Columns

I have a scenario where I need to move additional data in rows with the same primary key into additional columns. I've pulled data from our internal database via SQL Server Mgmt. Studio, and need to roll up certain data points respective to each primary key (candidate ID).

For instance: each candidate ID may have multiple work history or education history data sets--I need to roll up each change in the data into new, separate columns (See attached for current format & desired format).

Each candidate ID (roughly 100,000 unique IDs) may have more than 2 sets of education history and work history--it could be any number, so the VBA script will need to account for those possibilities (i.e. run until candidate ID changes).
I'm hoping this is possible--the case for this is for a data migration between our legacy system & a new system that needs the data rolled up into additional columns.