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.