>>There is an extra not needed ( before FROM. Remove it and the query should work.
>>
>>I'm not sure that's the only issue - the final SELECT in conjunction with the PIVOT seemed like it needed more. But without the data, wasn't positive. That's why I suggested the sanity check of the static base query first.
>
>Right, but since he got the SQL printed, he should be able to copy it, remove the extra ( and test. If it bombs, we'll see the next error.

Hmmm . . . Kevin says remove the extra ( after the FROM, Naomi says remove the extra ( before the FROM. Either way the result is the same with the same error as before. I can tell you that the inner SQL works as expected. Does it need an ORDER or GROUP clause? The @Columns parameter seems correct as evidenced by the printed string. However just to be sure I substituted in a fixed value [STP] and still get the same result. So as of right now I have :

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 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  

which produces

[HSIP], [PL], [SPR], [STP], [TAP]
;with cte as ( 
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 31
The name ';with cte as ( 
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.