Pivot columns to Rows keeping the Reference field

[noboffinme]

Hi Forum,
I have some weirdly formatted data as shown in the attachment.
It has normal columns but then has a column of Headings for the various Figures and then the figures.
I want to write the SQL to run within VBA in Excel to Transpose the Figure Headings and the figures themselves but keep the main field so I can join the data back together.
I've tried the Pivot Function but I don't think it's supported OR I'm not using it correctly??

I've enclosed some data to show the current and desired result.
Thanks for any help.