Pivot statement with Dynamic SQL and totals columns

The atteached code, supplied by an EE Expert works great.

The data is output as:
rep     January     February     etc
myrep  1000          1250          etc

Question...
Is there a way to add a totals column and if possible an average per month column?

New output desired...
rep     January     February     etc    Totals     AVG
myrep  1000          1250          etc     2250       1125
SELECT  v.rep,    
		Sum(v.[Gross Rev]) as GrossRev,     
		DATENAME(month, v.Funded) as Mth  ,  
		month(v.Funded) as MthI    
Into #temp    
FROM	CRMPROD_01.dbo.v_P_Reporting v    
WHERE	v.Funded is not null     
		and	v.Status <> 'Closed'     
		and	(YEAR(v.Funded) = YEAR(getdate()))     
		and	(MONTH(v.Funded) > MONTH(getdate())-@)    
Group by rep, v.Funded    
ORDER BY v.rep, month(v.Funded)  
  
--Create Dynamic SQl and Pivot   
declare @sql varchar(4000)  
declare @columns varchar(8000)    
  
select @columns = coalesce(@columns+',','') + '[' + Mth + ']'  
from #temp  
group by Mth, MthI  
order by MthI  
   
set @sql = '  
    SELECT [rep], ' + @columns + '  
    FROM    
    (SELECT [rep], [GrossRev], [Mth] as Mth from #temp) src  
     PIVOT  
    (Sum ([GrossRev]) FOR Mth IN (' + @columns + ') ) AS pvt'  
   
EXEC (@sql)  
  
Drop table #temp

Open in new window

lrbristerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
cyberkiwiConnect With a Mentor Commented:

SELECT  v.rep,    
		Sum(v.[Gross Rev]) as GrossRev,     
		DATENAME(month, v.Funded) as Mth  ,  
		month(v.Funded) as MthI    
Into #temp    
FROM	CRMPROD_01.dbo.v_P_Reporting v    
WHERE	v.Funded is not null     
		and	v.Status <> 'Closed'     
		and	(YEAR(v.Funded) = YEAR(getdate()))     
		and	(MONTH(v.Funded) > MONTH(getdate())-@)    
Group by rep, v.Funded    
ORDER BY v.rep, month(v.Funded)  
  
--Create Dynamic SQl and Pivot   
declare @sql varchar(4000)  
declare @columns varchar(8000)    
declare @totals varchar(8000)
declare @averages varchar(8000)
  
select	@columns = coalesce(@columns+',','') + '[' + Mth + ']',
		@totals  = coalesce(@totals+'+','') + 'isnull([' + Mth + '],0)',
		@averages  = coalesce(@averages+'+','') + 'isnull(abs(sign([' + Mth + '])),0)'
from #temp
group by Mth, MthI  
order by MthI

set @sql = '  
    SELECT [rep], ' + @columns +
		',[Total]=' + @totals +
		',[AVG]=1.0*(' + @totals + ')/isnull(nullif(' + @averages + ',0),0)
    FROM    
    (SELECT [rep], [GrossRev], [Mth] as Mth from #temp) src  
     PIVOT  
    (Sum ([GrossRev]) FOR Mth IN (' + @columns + ') ) AS pvt'  
   
EXEC (@sql)  
  
Drop table #temp

Open in new window

0
 
lrbristerAuthor Commented:
cyberkiwi
Wonderful...thanks.

If I can ask a quick follow-up...'

If this is in  astored procedure that I'll call from my VB code...is it necessary to have the "drop table #temp" at the end...or will that automatically drop after the SP executes?
0
 
cyberkiwiCommented:
If this is in an SP, no you don't have to drop it.
0
All Courses

From novice to tech pro — start learning today.