<CF_RenameQueryColumns>

This tag is a "wrapper" for the CFX_QueryColumns tag that allows you to easily deal with unusual column names in queries. It can do any of the following for you:

Make all of the column names for a query "safe" for use in Cold Fusion templates. For instance, you might have a query result set with column names like "My Column" and "My-Other-Column", which CF will not let you code with because they do not follow the rules for variable naming in CFML. This tag would automatically change those names to "MyColumn" and "MyOtherColumn" for you.
Make all of the column names for a query unique. For instance, you could have a query result set with two columns, both named "Col". This tag would automatically make the column names be "Col1" and "Col2" for you.
Rename the query columns based a prefix and numbers. For instance, you could have a query result set with seven columns, and that for whatever reason, you would rather refer to the column names "by number". This tag would automatically make the column names be "Col_1" through "Col_7" for you.

QUERY	Required. The name of the query you want to manipulate the column names of. Could be the results of a CFQUERY, CFLDAP, CFPOP, QueryNew(), etc.
ACTION	Optional. Available ACTIONs are "MAKESAFE" and "NUMBER". If ACTION="MAKESAFE", then the original column names will be replaced with "safe-for-CF" versions. This means that the first character of each column name must be a letter, and all the other characters must only contain letters, numbers, and the underscore (_) character. All other characters--including spaces, hyphens, and other symbols--will be stripped from each column name. If a column name is "" (blank), or if all of the characters in the original column name are illegal (for instance, if a column was named "1998"), then it will be remamed to "COL". If there were more than one such column, and MAKEUNIQUE="YES" (see below), then those columns might end up being named "COL_1", "COL_2", "COL_3", etc. If ACTION="NUMBER", then all of the original column names will be discarded and replaced with "COL_1", "COL_2", "COL_3", etc. This may make "looping" through column names easier for you in your CF code.
MAKEUNIQUE	Optional. If MAKEUNIQUE="YES", then this tag will "scan" the original query columns to see if any of the columns named appear more than once in the query result set (in other words, if any two columns have the same name) and renames them with numbers if needed. If three columns were all named "Company", for instance, the first column name will be left alone, but the second one will be renamed to "Company_2", and the third one will become "Company_3".
COLUMNPREFIX	Optional. If omitted, defaults to "COL". If you wanted this tag to rename columns to "COLUMN_1" instead of "COL_1" and so on, you would use COLUMNPREFIX="COLUMN".
USEUNDERSCORE	Optional. If omitted, defaults to "YES". If you wanted this tag to rename columns to "COL1" instead of "COL_1" and so on, you would use USEUNDERSCORE="No".
NEWQUERY	Optional. A name for the new, "corrected" version of the query. If omitted, the new query will simply overwrite (replace) the original version of the query.

Installation:
The CFX_QueryColumns tag must be present and registered on your CF server before you can use this tag, because this tag depends on its fuctionality. The CFX_QueryColumns tag is included as a seperate .zip with this tag for your convenience.

Example 1:
Making the column names "safe" and "unique" for use in your CF code: assume that the BadBlends table has three columns: "1999BLEND", "1998BLEND", and "BLEND TEXT". After the above code runs, the columns will be named "BLEND", "BLEND1", and "BLENDTEXT", respectively.

<CFQUERY NAME="MyQuery" DATASOURCE="Coffee Valley"> SELECT * FROM BadBlends </CFQUERY>  <CF_RenameQueryColumns QUERY="MyQuery" ACTION="MAKESAFE" NEWQUERY="MyNewQuery" MAKEUNIQUE="Yes" USEUNDERSCORE="No">

Example 2:
Numbering the columns: assume that the Blends table has two columns: "Blend_ID" and "Blend".

<CFQUERY NAME="MyQuery" DATASOURCE="Coffee Valley"> SELECT * FROM Blends </CFQUERY>  <CF_RenameQueryColumns QUERY="MyQuery" ACTION="Number" NEWQUERY="MyNewQuery"> The column names are:<BR> <CFOUTPUT>#MyNewQuery.ColumnList#<P></CFOUTPUT>  <CFLOOP QUERY="MyNewQuery"> <CFLOOP INDEX="This" FROM="1" TO="#ListLen(MyNewQuery.ColumnList)#"> <CFOUTPUT>#Evaluate("MyNewQuery.COL_#Variables.This#")#</CFOUTPUT> </CFLOOP><BR> </CFLOOP> The above code results in the following output:

The column names are:
COL_1,COL_2
1 Colombia Supremo
2 Espresso Roast
3 French Roast
4 Vienna Roast
5 Mocca-Java