Totals on PIVOT sp - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Totals on PIVOT sp

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-04-23 : 16:16:01

I am trying to include Totals row at the bottom of my grid.... I tried using C# code behind but I am hoping this can be done from within SQL. Any ideas on how to accomplish this? TY

ALTER PROCEDURE [dbo].[sp_Test]
AS
BEGIN
SELECT

COL_L,
TEAM,
S_LEVEL,
ISNULL([1], 0) AS Jan,
ISNULL([2], 0) AS Feb,
ISNULL([3], 0) AS Mar,
ISNULL([4], 0) AS Apr,
ISNULL([5], 0) AS May,
ISNULL([6], 0) AS Jun,
ISNULL([7], 0) AS Jul,
ISNULL(

, 0) AS Aug,
ISNULL([9], 0) AS Sep,
ISNULL([10], 0) AS Oct,
ISNULL([11], 0) AS Nov,
ISNULL([12], 0) AS Dec

FROM (
SELECT
COL_L,
TEAM,
S_LEVEL,
DATEPART(M, OPEN_T) AS AcqMonth,

COL_L AS IMPACT,
I_NO

FROM dbo.ALL_YTD

WHERE COL_L like 'P1%' AND SEV_CODE LIKE 'P1%'
group by COL_L, TEAM,S_LEVEL, I_NO,DATEPART(M, OPEN_T)

)as p
PIVOT (
COUNT(I_NO) for AcqMonth in ([1], [2], [3], [4], [5], [6], [7],

, [9], [10], [11], [12])
)as pvt
END

desired result:

COL_LEVEL TEAM S_LEVEL Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
P1/2 TEAM1 Jesse James 1 1 0 0 0 0 0 0 0 0 0 0
P1/3 TEAM2 Frank James 1 0 2 0 0 0 0 0 0 0 0 0
P1/3 TEAM3 Clint Eastwood 0 0 0 1 0 0 0 0 0 0 0 0
P1/3 TEAM4 John Wayne 0 0 1 0 0 0 0 0 0 0 0 0
Totals 2 1 3 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 16:21:09

you must include the total via UNION ALL inside the main query over which you apply the PIVOT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 19:37:57

see an illustration here



declare @t table
(
id int identity(1,1),
Category varchar(20),
Yr int,
Val int
)

insert @t (Category,Yr,Val)
select 'Cat1',2011,15 union all
select 'Cat1',2010,22 union all
select 'Cat2',2011,30 union all
select 'Cat3',2011,19 union all
select 'Cat1',2011,32 union all
select 'Cat2',2010,44 union all
select 'Cat4',2011,30 union all
select 'Cat1',2010,14 union all
select 'Cat3',2011,35 


select *
from
(
select Category,cast(Yr as varchar(4)) AS Yr,Val
from @t
union all
select Category,'Total',sum(Val)
from @t
group by Category
)t
pivot (sum(Val) for Category IN ([Cat1],[Cat2],[Cat3],[Cat4]))p


output
-------------------------------------
Yr	Cat1	Cat2	Cat3	Cat4
2010	36	44	NULL	NULL
2011	47	30	54	30
Total	83	74	54	30

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-04-24 : 09:54:39

Great Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 21:38:45

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources