>>;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.
; 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) ) select * from t1 pivot (sum(NET) for FundType in ([ASTP], [RTA], [HURF26], [DIFO], [Local], [STP])) pvtbut 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 abovebut the samples all leave me befuddled.