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
Go to Top of Page
   

- Advertisement -