Stuck on Unpivot / Pivot Combo SQL Server 2008

Question

It seems that I cannot solve this problem when it seems to me that in SQL Server 2008 I need a combination of anchor points and perforce:

I have a table as follows:

Sale | Month | Count | Budgeted | Actual ------------------------------------------------ NewSale | 1 | 120 | 45.23 | 50.10 NewSale | 2 | 30 | 3.10 | 1.2 NewSale | 3 | 70 | 45.00 | 100.32 

I need to twist so that the months are columns, but are not open, so I get Count, Budgeted, Actual as rows, so it is ...

 Type | 1 | 2 | 3 ----------------------------------- Count | 120 | 30 | 70 Budgeted | 45.23 | 3.10 | 45.00 Actual | 50.10 | 1.2 | 100.32 

I have tried this so far, but I cannot figure out how to place the anchor point here:

 select * from YTD pivot ( sum([Count]), sum([Budgeted]), sum([Actual]) for [Month] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) as figures 

This gives me a syntax error, since you cannot have more than one calculation in the code (as far as I understood from the error.

Help !!!

Answer 1

 declare @T table ( Sale varchar(10), [Month] int, [Count] int, Budgeted money, Actual money ) insert into @T values ('NewSale', 1, 120, 45.23, 50.10), ('NewSale', 2, 30, 3.10, 1.2), ('NewSale', 3, 70, 45.00, 100.32) select [Type], [1], [2], [3] from ( select [Month], cast([Count] as money) as [Count], Budgeted, Actual from @T ) as T unpivot ( Value for [Type] in ([Count], Budgeted, Actual) ) as U pivot ( sum(Value) for [Month] in ([1], [2], [3]) ) as P 

Try SE-Data .

Answer 2

You need to make 3 choices and combine the result to get a table that looks like this.

for instance

  Select 'Count' as [Type], 120 AS 1, 40 AS 2, 70 AS 3 UNION ALL Select 'Budgeted' as [Type], 45.23 AS 1, 3.10 AS 2, 45.00 AS 3 UNION ALL Select 'Actual' as [Type], 50.1 AS 1, 1.2 AS 2, 100.32 AS 3 

Will look like your example

So just replace each of these elements with a peony that returns the desired string, and you GTG