>Hi -
>I'm not sure if this is possible but thought I would at least ask. There are 2 tables [Approved] and [Proposed]. They are identical in structure:
>ProjectID, Amount, Fundtype. The number of possible fundtype values is unknown. We want to create a SELECT statement that will display the difference between the two tables with each fundtype being displayed as a column. Something like this:
>
>Project | Fundtype1 | Fundtype2 | Fundtype3 | Fundtype4, etc.
>P1 | 0 | 14 | 0 | -6 |
>P2 | 5 | -2 | 8 | -1 |
>
>In this example P1 has the same amount of Fundtype1 in both tables, so the difference is 0. It also has 14 more of Fundtype2 in [Proposed] than in [Approved], so the difference is 14 (Proposed minus Approved). and so on. P1 does not have Fundtype3 in either of the two tables. etc. I could build this result table programmatically with a loop and a lot of selects but I was wondering if there is a way to do it all in one select.
>
>Thanks for any suggestions.

Well, this is a simple dynamic PIVOT here.

I'll show you the static pivot example for your case, it's easy to turn into dynamic:

;with cte as (select coalesce(P.ProjectId, A.ProjectId) as ProjectId, coalesce(P.Amount,0) - coalesce(A.Amount,0) as Amount,
coalesce(P.FundType, A.FundType) as FundType
from Proposed P full join Approved A on P.ProjectID = A.ProjectID and P.FundType = A.FundType)

select * from CTE PIVOT (sum(Amount) for FundType IN ([FundType1], [FundType2], etc.)) pvt

Turning static pivot into dynamic is a relatively simple task.

See my article on this topic:

http://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx