>>>Thanks Naomi, I figured if it could be done I would get the help here. There is one additional quirk that I failed to mention. I need to select a specific AmendmentNumber from both tables. So in my real world case I ended up with this:
>>>

>>>;with cte as (
>>>select P.AmendmentNumber, coalesce(P.TipId, A.TipId) as TipId, coalesce(P.Amount,0) - coalesce(A.Amount,0) as Amount,
>>>coalesce(P.FundType, A.FundType) as FundType
>>>from TIP_AmendmentProposedFunding P 
>>>full join TIP_AmendmentApprovedFunding A 
>>>on P.TipID = A.TipID and P.FundType = A.FundType
>>>where P.AmendmentNumber = 2014.01 and A.AmendmentNumber = 2014.01
>>>)
>>>
>>>select * from CTE PIVOT (sum(Amount) for FundType IN ([ASTP], [RTA], [HURF26], [DIFO], [Local], [STP])) pvt
>>>

>>>This gets pretty close but the computation is off. here is some real data.
>>>
>>>APPROVED
>>>Keyid AmendmentNumber TipID AMOUNT FundType YEAR PHASE
>>>296 2014.01 5.12 8000 NH 2013 DR
>>>297 2014.01 5.12 16500 RTA 2017 D
>>>298 2014.01 5.12 6500 ASTP 2017 D
>>>
>>>PROPOSED
>>>Keyid AmendmentNumber TipID AMOUNT FundType YEAR PHASE
>>>332 2014.01 5.12 8000 NH 2013 DR
>>>333 2014.01 5.12 16500 RTA 2017 D
>>>334 2014.01 5.12 6500 ASTP 2017 D
>>>336 2014.01 5.12 42900 RTA 2018 C
>>>337 2014.01 5.12 43583 ASTP 2018 C
>>>
>>>For this project, the resultant difference for ASTP is 37083, whereas it should be 43583 because the two 6500 values cancel each other out. So maybe there needs to be a couple of sums and group by in the first part of the select? It is still fairly greek to me.
>>>
>>>Its interesting that the keyword PIVOT does not appear in my 800 page SQL handbook. Neither does pvt which I assume is just an alias assigned to the result. Correct?
>>
>>You're right, looks like you first need to group by FundType and then join.
>>
>>PIVOT was added in SQL 2005. Prior to that (in SQL 2000) you would use case based pivot instead.
>
>OK, this seems to get me to where I need to be statically.
>

>; > ) 
>select *with t1 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,
>sum(coalesce(p.AMOUNT,0)- coalesce(A.Amount,0)) as NET 
>from TIP_AmendmentProposedFunding P
>full outer join TIP_AmendmentApprovedFunding A 
>on P.TipID = a.TipID and p.AmendmentNumber = a.AmendmentNumber and p.YEAR = a.year 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)=2014.01 ) 
>group by coalesce(p.amendmentnumber, a.amendmentnumber),ItemNumber,coalesce(p.tipid, a.tipid),coalesce(p.fundtype, a.fundtype)

>from t1 pivot (sum(NET) for FundType in ([ASTP], [RTA], [HURF26], [DIFO], [Local], [STP])) pvt 
>

>but the dynamic part eludes me even after studying your (and others) samples. Can you describe the principle involved? What exactly is the approach? I can see that the dynamic string needs to be built with something like

SELECT distinct FundType FROM ....the result in the first part of the above

but the samples all leave me befuddled.
>Thanks Naomi, you've been a great help.

Actually, I would approach the first part also differently - I will do GROUP BY separately (2 separate CTE) and then JOIN.

Anyway, here is a solution from the top of my head - you caught me in the middle of several things:

declare @SQL varchar(max), @Columns VARCHAR(max)

set @columns = stuff((select  ', ' + quotename(FundType) 
FROM ( SELECT FundType from TIP_AmendmentProposedFunding P UNION SELECT FundType FROM TIP_AmendmentDetails) S 
ORDER BY FundType FOR XML PATH('').type).value('.', 'varchar(30)')), 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,
coaleasce(p.AMOUNT,0) - coalesce(A.Amount,0) as NET 
from (SELECT AmendementNumber, FundType, TipId, SUM(Amount) as Amount
FROM
 TIP_AmendmentProposedFunding
GROUP BY AmendementNumber, FundType, TipId
) P 
full outer join (SELECT AmendementNumber, FundType, TipId, SUM(Amount) as Amount
FROM  TIP_AmendmentApprovedFunding 
GROUP BY AmendementNumber, FundType, TipID) A 
on P.TipID = a.TipID and p.AmendmentNumber = a.AmendmentNumber and p.YEAR = a.year 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)=2014.01 )  -- 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);

The above is from the top of my head, so may need minor adjustments.