Ok I've managed to get this performing reasonably well with a combination of changes. I've kept the 3 queries separate as I wasn't getting anywhere combining them in a single query.

I've kept the first query as is. I believe I can get further performance improvement out of this but for now it will do.

The first performance improvement was in relation to the data itself. It turns out not all Applicant have summaryData (about 25%) therefore I was able to delete these before the next query.

The next performance improvement was for Matt Millers suggestion of not using the wildcard (. //*). This improved performance quite a bit. Additionally I am now loading the second query into a temp table rather than a table variable. This does not improve performance of the load but as great benefit in the final PIVOT query.

I could not find a useful Index to apply to the temp table. In fact it seemed to hinder performance. I noticed the without a index the Pivot query was choosing a parallel plan, but with one it was not. This is the crux of the final performanance improvement. It turns out that if you run the query with a table variable you do not get the parallel plan, but you do with temp table, taking the query for about 50mins to 7mins.

Thanks for the help,

Paul