CFLib.org – Common Function Library Project

Description:
Returns a list of unique values from a query column with the option of case sensitive or not.

Return Values:
Returns a string.

Example:

--Not Case sensitive
#uniqueValueList(queryName, "queryColumn")#

-- Case Sensitive
#uniqueValueList(queryName, "queryColumn", 1)#

Parameters:

Full UDF Source:

<cfscript>
/**
 * Returns a list of unique values from a query column.
 * 
 * @param queryname 	 Query to scan. (Required)
 * @param columnname 	 Column to use. (Required)
 * @param cs 	 If true, the unique list will check the case of the values. Defaults to false. (Optional)
 * @return Returns a string. 
 * @author Nick Giovanni (&#110;&#103;&#105;&#111;&#118;&#97;&#110;&#110;&#105;&#64;&#103;&#109;&#97;&#105;&#108;&#46;&#99;&#111;&#109;) 
 * @version 1, March 27, 2007 
 */
function uniqueValueList(queryName, columnName) {
	var cs = 0; 
	var curRow = 1;
	var uniqueList = "";  
	
	if(arrayLen(arguments) GTE 3 AND isBoolean(arguments[3])) cs = arguments[3]; 
	
	for(; curRow LTE queryName.recordCount; curRow = curRow +1){
		if((not cs AND not listFindNoCase(uniqueList, trim(queryName[columnName][curRow]))) OR (cs AND not listFind(uniqueList, trim(queryName[columnName][curRow])))){
			uniqueList = ListAppend(uniqueList, trim(queryName[columnName][curRow]));
		}
	}
	return uniqueList; 
}
</cfscript>