Kevin is absolutely right - you have a typo here:

;with cte as (
SELECT * 
FROM (
SELECT sponsor, fundtype, amount

There is an extra not needed ( before FROM. Remove it and the query should work.





>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