Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

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

0
lrbrister
Asked:
lrbrister
  • 2
1 Solution
 
cyberkiwiCommented:

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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now