See Making a list and checking it twice for explanation of type and value I used.

I've fixed couple of problems in your code:

use TipData

declare @SQL varchar(max), @Columns VARCHAR(max), @AmendmentNumber varchar(7)
set @AmendmentNumber = '2014.01'

set @columns = stuff((
select  ', ' + quotename(FundType) 
FROM ( 
SELECT FundType from TIP_AmendmentProposedFunding P 
WHERE AmendementNumber =  @AmendmentNumber 
UNION
SELECT FundType FROM TIP_AmendmentApprovedFunding WHERE AmendementNumber =  @AmendmentNumber 
) S
WHERE AmendmentNumber = @AmendmentNumber
ORDER BY FundType 
FOR XML PATH('')), 1,2,'')  -- type and value were used in case the FundType contained some characters that may choke XML


-- list of new columns

set @SQL = 
';with cte as (
select coalesce(p.amendmentnumber, a.amendmentnumber) as AmendmentNumber,
d.ItemNumber,
coalesce(p.tipid, a.tipid) as TipID,
coalesce(p.fundtype, a.fundtype) as FundType,
coalesce(p.AMOUNT,0) - coalesce(A.Amount,0) as NET 
FROM (SELECT AmendmentNumber, FundType, TipId, SUM(Amount) as Amount
FROM TIP_AmendmentProposedFunding WHERE AmendementNumber =  @AmendmentNumber 
GROUP BY AmendmentNumber, FundType, TipId  ) P 
full outer join (SELECT AmendmentNumber, FundType, TipId, SUM(Amount) as Amount
FROM  TIP_AmendmentApprovedFunding WHERE AmendementNumber =  @AmendmentNumber 
GROUP BY AmendmentNumber, FundType, TipID) A 
on P.TipID = a.TipID and p.AmendmentNumber = a.AmendmentNumber and p.FundType = a.fundtype
full join TIP_AmendmentDetails d 
ON d.AmendmentNumber = p.amendmentnumber and d.TipID = p.tipid
  -- do you need that number only - if yes, add this also into derived tables
)

SELECT * FROM cte PIVOT (sum(NET) FOR FundType IN (' + @Columns + ')) pvt'

--print @Columns; -- examine the list of generated fund types
--PRINT @SQL; -- examine the SQL

execute sp_ExecuteSQL @SQL   N' @AmendmentNumber  varchar(7)',  @AmendmentNumber ;

Don't embed parameters directly into SQL.


>Yep, it works with some minor adjustments. The black light is now only dark gray. Here is what I have now.
>

>use TipData
>
>declare @SQL varchar(max), @Columns VARCHAR(max), @AmendmentNumber varchar(7)
>set @AmendmentNumber = '2014.01'
>
>set @columns = stuff((
>select  ', ' + quotename(FundType) 
>FROM ( 
>SELECT FundType, AmendmentNumber from TIP_AmendmentProposedFunding P 
>UNION
>SELECT FundType, AmendmentNumber FROM TIP_AmendmentApprovedFunding
>) S
>WHERE AmendmentNumber = @AmendmentNumber
>ORDER BY FundType 
>FOR XML PATH('')), 1,2,'') 
>
>-- list of new columns
>
>set @SQL = 
>';with cte as (
>select coalesce(p.amendmentnumber, a.amendmentnumber) as AmendmentNumber,
>d.ItemNumber,
>coalesce(p.tipid, a.tipid) as TipID,
>coalesce(p.fundtype, a.fundtype) as FundType,
>coalesce(p.AMOUNT,0) - coalesce(A.Amount,0) as NET 
>FROM (SELECT AmendmentNumber, FundType, TipId, SUM(Amount) as Amount
>FROM TIP_AmendmentProposedFunding 
>GROUP BY AmendmentNumber, FundType, TipId ) P 
>full outer join (SELECT AmendmentNumber, FundType, TipId, SUM(Amount) as Amount
>FROM  TIP_AmendmentApprovedFunding 
>GROUP BY AmendmentNumber, FundType, TipID) A 
>on P.TipID = a.TipID and p.AmendmentNumber = a.AmendmentNumber and p.FundType = a.fundtype
>full join TIP_AmendmentDetails d 
>ON d.AmendmentNumber = p.amendmentnumber and d.TipID = p.tipid
>where (coalesce(p.amendmentnumber, a.amendmentnumber)=' + @AmendmentNumber + ')  -- do you need that number only - if yes, add this also into derived tables
>)
>
>SELECT * FROM cte PIVOT (sum(NET) FOR FundType IN (' + @Columns + ')) pvt'
>
>--print @Columns; -- examine the list of generated fund types
>--PRINT @SQL; -- examine the SQL
>
>EXECUTE (@SQL);
>
>

>I had to remove the TYPE in the first SQL as it was causing syntax problems and I didn't understand its purpose. I also had to remove the YEAR from the main SQL as the field was not included in the derived tables and with the GROUP BY appearing earlier it was no longer needed. I changed the fixed value 2014.01 to a parameter. Why do you think it needs to be included in the derived tables? Seems like it really only needs to be filtered at the end.
>
>Thanks again. I really learned a lot with this one. New to me was STUFF, FOR XML PATH, PIVOT, and a refresher on COALESCE.