Advanced Pivot with totals rows X Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Advanced Pivot with totals rows X Columns

Author

Topic

jooorj
Posting Yak Master

126 Posts

Posted - 2011-04-24 : 13:01:48

Hello everybody :)
------------------------------------------------------------
empno month1 month2 month3 all months
1001 162.333650 250.000000 450.000000 g
1002 300.000000 350.000000 500.000000 g
1003 200.000000 450.000000 600.000000 g
-----------------------------------------------------------
Total: 662.333650 1050.000000 1550.000000 g
------------------------------------------------------------
I want to replace all months (g) with total month1+month2+month3
I used the following pivot Script

select emp_no,[1] ,[2],[3],'g'
from(

select case when emp_no is null then
'Total:>' else
CAST (emp_no as nvarchar) end as emp_no
,months,sum(salary) as salary from AAA
group by cube( months, emp_no)

) sourced
pivot
(
sum(salary) for months
in([1],[2],[3])
)pvt

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-24 : 13:10:04

Or maybe this isn't a question.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jooorj
Posting Yak Master

126 Posts

Posted - 2011-04-25 : 03:15:56

where is the experts ? until now I could not find an developers experts !
inspit of all my questions are simple but I could not find the right solutions .
so sorry to say that.
but it is the truth.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-25 : 04:29:33

Try this

select emp_no,[1] ,[2],[3],g,[1] +[2]+[3] as total
from
(
select emp_no,[1] ,[2],[3],'g' as g
from(
select case when emp_no is null then
'Total:>' else
CAST (emp_no as nvarchar) end as emp_no
,months,sum(salary) as salary from AAA
group by cube( months, emp_no)
) sourced
pivot
(
sum(salary) for months
in([1],[2],[3])
)pvt
) t

Madhivanan

Failing to plan is Planning to fail

jooorj
Posting Yak Master

126 Posts

Posted - 2011-04-25 : 05:47:40

Big Thanks for you MR.madhivanan
It Worked 100%
Thank you.

jooorj
Posting Yak Master

126 Posts

Posted - 2011-04-25 : 06:39:14

good code but I want to use script more functionality like (Rollup + Cube + Grouping SETs, Compute by)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-25 : 09:17:22