Using Transform within Sp

[holly]

Hi

Pls Help, I have this transform stmt within a Sp which I wish to use
inorder to output the results similar to a crosstab qry...so my ? is can
I use this code and if so why do I recieve an error

if @ReportType = 'MonthlyCounts'
begin
	TRANSFORM  count(CQuote_int)as Quotes
	SELECT DateName(Month, ReportDate) Month
	from #transactionstable
	WHERE (DATEPart(YYYY, ReportDate) = DATEPart(YYYY, GetDate())
	GROUP BY DateName(Month, ReportDate), datePart(MM,ReportDate)
	ORDER BY order by datePart(MM,ReportDate)

PIVOT transactionstable.Month;
		
	/*
	select DateName(Month, ReportDate) Month, sum(CQuote_int)Quotes ,sum(CReferral_int)Referrals , sum(CNewBus_int) Policies  
		from #transactionstable
	where DATEPart(YYYY, ReportDate) = DATEPart(YYYY, GetDate())
	group by DateName(Month, ReportDate), datePart(MM,ReportDate)
	order by datePart(MM,ReportDate)
	*/
end

The errors are

Server: Msg 170, Level 15, State 1, Procedure Tonyg_ReportChart_GetBDOnlineMonthlyCountsNew1, Line 123
Line 123: Incorrect syntax near 'TRANSFORM'.
Server: Msg 156, Level 15, State 1, Procedure Tonyg_ReportChart_GetBDOnlineMonthlyCountsNew1, Line 127
Incorrect syntax near the keyword 'GROUP'

Ta very much

[NeedSomeAnswers]

You say you are using this statement in an SP, so i take it you are using SQL Server ???

If so then you can't use the Transform Statement it doesn't exist in SQL Server it is an Access Specific statement

[holly]

Hi, Thanks for replying!! is there or do you know if this can be done in sql
using any other stmt??

[smendoza]

Take a look at this.
http://www.sqlteam.com/article/dynam...s-pivot-tables
It's a pretty good example.