In one of our older database tables we have a field named “ROLLUP”.
For SQL SERVER 2008 this is a reserved word. When writing dynamic SQL with group by statements using the column name “rollup” coldfusion will throw a syntax error indicating there is a problem near “rollup” or “,”.
The solution is to replace the keyword with the keyword in brackets.
CFSET mylist = “rollup,column2,column3,column4”
* this may come from form elements passed or other selections that make it a dynamic list
, sum myValue
group by #rereplace(mylist, “rollup”, “[rollup]”, “1”)#
order by #mylist#
group by [rollup],column2,column3,column4