Trying to transform data into different data structure using direct query

IWallis  I found the bug

Apprently, you need to put a single space between semicolon and With. SMMS is indifferent but PQ is not.

So not like

;WITH CTE1 as

but

; WITH CTE1 AS

I have successfully debugged two versions

; with cte1 as(
select top 1 [source_system_id] from
[schema].tbl)
select * from cte1

declare @t1 as table (source_system_id int)
; with cte1 as(
select top 1 [source_system_id] from
[schema].bl)
insert into @t1
select * from cte1

select * from @t1

So please give this a try

; WITH cte_mosm (breakoutid, m1, m2)
     AS (SELECT *
         FROM   (SELECT mosmmetric.metricname AS MetricName,
                        breakoutid,
                        value                 AS MetricValue
                 FROM   mosmmetricvalue
                        LEFT JOIN mosmmetric
                               ON mosmmetricvalue.metricid = mosmmetric.metricid
                 WHERE  year = '2021') AS MoSM2021NP
                PIVOT (Max(metricvalue)
                      FOR metricname IN (
                [Sourceable spend as a percent of revenue],
                [Managed spend as a percent of sourceable spend])) AS
                mosm2021p)
SELECT breakoutid,
       Format(m1, 'P')AS 'Sourceable spend as a percent of revenue',
       Format(m2, 'P')AS 'Managed spend as a percent of sourceable spend'
FROM   cte_mosm