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

Did you try running

;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

This should work.

Also, it should be
execute (@SQL)

Note the parenthesis.