Need SQL guru for TRANSFORM PIVOT statement

From: mdeschen <l0x__at_msn.com>
Date: 22 Jul 2003 12:56:24 -0700
Message-ID: <fa59c606.0307221156.450d68ce@posting.google.com>

I have the following query and an output example:

SELECT Sum(tblReal.Amount) AS RealAmount, Sum(tblForecast.Amount) AS ForecastedAmount, Sum(tblReal.Amount-tblForecast.Amount) AS Variation1, Sum(tblBudget.Amount) AS BudgetedAmount, Sum(tblReal.Amount-tblBudget.Amount) AS Variation2 FROM tblBudget INNER JOIN (tblReal INNER JOIN tblForecast ON
(tblReal.Mois = tblForecast.Mois) AND (tblReal.Location =

tblForecast.Location) AND (tblReal.SubAccount =
tlbForecast.SubAccount) AND (tlbReal.MainAccount =
tblForecast.MainAccount) AND (tblReal.IDMonth = tblForecast.IDMonth))

ON (tblBduget.Location = tblReal.Location) AND (tblBudget.SubAccount = tblReal.SubAccount) AND (tblBudget.MainAccount = tblReal.MainAccount) AND (tblBudget.Month = TblReal.Month) AND (tblBudget.IDMonth = tblReal.IDMonth)
GROUP BY tblBudget.IDMonth, tblBudget.Month, tblBudget.MainAccount, tblBudget.Location
HAVING (((tblBudget.MainAccount)=63410) AND
((tblBudget.Location)="Halifax"))

ORDER BY tblBudget.IDMonth;

In reality what I calculate with the query is the following but note that all I see (from query defined higher) is the last 5 columns. (And thats all I want to see)

IDM Month MAcnt Location RAmnt FAmnt Var1 BAmnt Var2

---

1     November	63410	Halifax  1500	1750	-250	1600	-100
2     December	63410	Halifax  1750	1750	0	1750	0
3     January	63410	Halifax  1600	1650	-50	1500	100
4     Febuary	...	...	 ...	...	...	...	...
5     March	...	...	 ...	...	...	...	...
6     April	...	...	 ...	...	...	...	...
7     May	...	...	 ...	...	...	...	...
8     June	...	...	 ...	...	...	...	...
9     July	...	...	 ...	...	...	...	...
10    August	...	...	 ...	...	...	...	...
11    September	...	...	 ...	...	...	...	...
12    October	...	...	 ...	...	...	...	...

Now, I have all the values I want. Everything is calculated right with this query. BUT, it is not displayed the way I want.

I would like to just flip the output over so I get months as headers and RealAmmount, ForecastedAmount, Variation1, BudgetedAmount and Variation2 as rows.

I guess the TRANSFORM PIVOT statement could help but I cant figure out what to do for the transform part since my data doesnt need to be transformed. The data is right, it's just not displayed correctly.

Please help me. It's my biggest SQL challenge ever. (maybe I'm dumb...lol)

Also if there's a more appropriate group for this question please let me know. I searched a bit but didnt found a SQL specific group.

Thanks,

Martin Received on Tue Jul 22 2003 - 14:56:24 CDT