How to convert Query column to a list in ColdFusion
44,349
Solution 1
There is a built-in function to do that: ValueList
<cfset myList = ValueList(query.columnname)>
As with all of the list functions, there's an optional delimiter attribute.
<cfset myList = ValueList(query.columnname,"|")>
If you need the values in the list to have double-quotes around them, use QuotedValueList.
<cfset myList = QuotedValueList(query.columnname)>
Solution 2
You can also access a query's columns as arrays directly without any conversion if that works for what you're trying to do:
qry.col[1] // col field of first record
qry.col[2] // col field of second record
...
or
qry["col"][1] // col field of first record
qry["col"][2] // col field of second record
A CF query object is really an associative array of columns... weird but occasionally useful.
Solution 3
How about in a case like this:
<cfset SummaryQuery = Evaluate('getReportData' & summaryName & 'Summary') />
<cfset TypeList = ArrayToList(SummaryQuery[subsectionName & 'Type']) />
vs.
<cfset QueryColumn = SummaryQuery[subsectionName & 'Type'] />
<cfset TypeList = ValueList(QueryColumn) />
Related videos on Youtube
Author by
erikvold
Updated on July 09, 2022Comments
-
erikvold almost 2 years
I'm trying to convert ColdFusion query column to a list, what is the best way to do so?
I thought that there is a built in function that allows one to easily convert a query's column in to a list, if there is what is it?
-
Tyler Clendenin about 13 yearsErik, your question is a bit old so you prolly already figured it out, but for posterity:
ArrayToList(QueryName["ColumnName"], ",")
-
-
Tyler Clendenin about 13 yearsEven though this is closed, don't know why, I feel compelled to place this answer, ArrayToList(QueryName["ColumnName"], ",").
-
ale over 12 yearsWhy in the world are you offering these convoluted solutions? What's wrong with ValueList()?
-
bpanulla over 12 yearsTreating the column as an array requires no conversion and is faster for large recordsets. ValueList() takes some non-zero amount of time to convert, and what you get out of it is a string. Working with a string list now takes even more time. Why convert something from an array to a list and then back to some sort of iterable construct just to use it again?
-
ale over 12 years@bpanulla: Fair enough, but the asker did specifically ask for a list.
-
bpanulla over 12 yearsIf the poster asked "what's the best pair of scissors to use to cut down a tree?" wouldn't you feel obliged to suggest a chainsaw? Just saying ;)
-
ale over 12 yearsWell, asking "what's the best" anything is a subjective question and likely to be closed, but I get your meaning.
-
David Faber about 11 yearsOne might want a list to use in a future query with an IN clause. I've done this in CF many times to avoid using CFQUERY within a loop over another query.
-
Jared Beck about 11 years-1 because this answer is misleading. Using dot-notation on a query does not actually return an array.
IsArray(qry.col)
returns false. You can access cells with[]
but it's not actually a CF array. -
Matthew Friedman almost 6 yearswhy not just used the built in function query.ColumnList
-
ale almost 6 years@MatthewFriedman: query.ColumnList gives you a list of the columns. ValueList() gives you a list of the value in every row for a specific column. Two very different usecases.