OK Folks. Thanks for the replies. I had already tried the double single quote thing and still got the same error so I assumed it wasn't working. I now see it does work and I have an additional problem. My code:

use TipData

declare @SQL nvarchar(max), @Columns VARCHAR(max)

set @columns = stuff((
select ', ' + quotename(FundType) 
FROM ( 
SELECT FundType 
FROM LookupFundtypes P 
WHERE OA=1) S
ORDER BY FundType 
FOR XML PATH('')), 1,2,'') 

print @Columns

set @SQL = 
';with cte as (
SELECT * 
FROM (
SELECT sponsor, fundtype, amount
from TIP_ApprovedFunding f
inner join TIP_Projects p on p.TipID = f.TipID
where p.ApprovedStatus = ''Active'' 
)
 
 select * from cte  
 PIVOT
 (
   Sum(amount) FOR [FundType] IN (' + @Columns + ')
 ) 
 AS PivotTableAlias'
 
print @SQL
execute @SQL 

Is producing this result:

[HSIP], [PL], [SPR], [STP], [TAP]
;with cte as (
SELECT * 
FROM (
SELECT sponsor, fundtype, amount
from TIP_ApprovedFunding f
inner join TIP_Projects p on p.TipID = f.TipID
where p.ApprovedStatus = 'Active' 
)
 
 select * from cte  
 PIVOT
 (
   Sum(amount) FOR [FundType] IN ([HSIP], [PL], [SPR], [STP], [TAP])
 ) 
 AS PivotTableAlias

Msg 203, Level 16, State 2, Line 34
The name ';with cte as (
SELECT * 
FROM (
SELECT sponsor, fundtype, amount
from TIP_ApprovedFunding f
inner join TIP_Projects p on p.TipID = f.TipID
where p.ApprovedStatus = 'Active' 
)
 
 select * from cte  
 PIVOT
 (
   Sum(amount) FOR [FundType] IN ([HSIP], [PL], [SPR], [STP], [TAP])
 ) 
 AS PivotTableAlias' is not a valid identifier.

Note the error at the end. This is only my second attempt to using the PIVOT function (thanks Naomi) and I can't figure out what is still wrong. Can any of you experts see what I have missed? The desired result is a matrix with sponsors in the left column, fundtypes across the top, and the sum of amount at the intersection.

Thanks