How to Pivot when select having OPTION (MAXRECURSI

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)

How to Pivot when select having OPTION (MAXRECURSI

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2014-08-20 : 06:00:32

Hi All,

I have below statement:-


select Age,Race,Years,
Apr=sum(Isnull([04],0)),May=sum(Isnull([05],0)),Jun=Sum(Isnull([06],0)),
	Jul=Sum(Isnull([07],0)),Aug=Sum(Isnull([08],0)),Sep=Sum(Isnull([09],0)),
	Oct=Sum(Isnull([10],0)),Nov=Sum(Isnull([11],0)),[Dec]=Sum(Isnull([12],0)),
	Jan=Sum(Isnull([01],0)),Feb=Sum(Isnull([02],0)),Mar=Sum(Isnull([03],0))  
from
(
	select 
		Age,Race,	
		Left(DateIssued,4) as Years,Right(DateIssued,2) as Months
	from
	(
		SELECT Age, Race, DateIssued, Salary
		from tableA
		where Left(DateIssued,4) +'-' + Right(DateIssued,2) +'-01'  between '2014-04-01' and '2015-03-31'
		ORDER BY F.[AmountGBPCur] DESC  
		OPTION (MAXRECURSION 10000) 
	)A
) B
pivot (sum(Salary) 
for Months in ([04],[05],[06],
[07],[08],[09],[10],[11],[12],[01],
[02],[03])) as SumSalaryPerMonth
group by Age,Race,Years

The query cannot run due to OPTION (MAXRECURSION ... It's compulsory for me to store all the result into a temporarily table first then only do pivot?

Please advise.

Thank you.

Regards,
Micnie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-20 : 07:16:43

I can't see the recursive query.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources