Basically has 3 versions of this for each of the desired columns ( ActiveCases, Last seen, & Refresh) 
SELECT   *
FROM    (SELECT   LocationName, ActiveCases
         FROM    [FairWarning_Extract].[dbo].[vw_Adastra_Live]) AS s PIVOT (SUM(s.ActiveCases) FOR LocationName IN ([Alston MIU (PCAS)], [Clinics (PCAS)],
         [Kendal (PCAS)], [Maryport (PCAS)], [Penrith MIU (PCAS)])) AS PVT

then just cross applied them in a view with a few aliases on the column names as per below:

SELECT   ac.[Alston MIU (PCAS)] AS Alston_MIU_PCAS, ac.[Clinics (PCAS)] AS Clinics_PCAS, ac.[Kendal (PCAS)] AS Kendal_PCAS, ac.[Maryport (PCAS)] AS Maryport_PCAS,
         ac.[Penrith MIU (PCAS)] AS Penrith_MIU_PCAS, ls.[Alston MIU (PCAS)] AS Alston_MIU_PCAS_Last_Seen, ls.[Clinics (PCAS)] AS Clinics_PCAS_Last_Seen,
         ls.[Kendal (PCAS)] AS Kendal_PCAS_Last_Seen, ls.[Maryport (PCAS)] AS Maryport_PCAS_Last_Seen, ls.[Penrith MIU (PCAS)] AS Penrith_MIU_PCAS_Last_Seen,
         r.[Alston MIU (PCAS)] AS Alston_MIU_PCAS_Refresh, r.[Clinics (PCAS)] AS Clinics_PCAS_Refresh, r.[Kendal (PCAS)] AS Kendal_PCAS_Refresh,
         r.[Maryport (PCAS)] AS Maryport_PCAS_Refresh, r.[Penrith MIU (PCAS)] AS Penrith_MIU_PCAS_Refresh
FROM    dbo.vw_Adastra_Active_Cases AS ac CROSS JOIN
         dbo.vw_Adastra_Last_Seen AS ls CROSS JOIN
         dbo.vw_Adastra_Refresh_Date AS r

Sure there is a slicker way to do it but it works for what i need

Thanks Again.

P