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:

/**
 * 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 (ngiovanni@gmail.com) 
 * @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; 
}