22

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?

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
erikvold
  • 15,988
  • 11
  • 54
  • 98
  • 2
    Erik, your question is a bit old so you prolly already figured it out, but for posterity: `ArrayToList(QueryName["ColumnName"], ",")` – Tyler Clendenin Apr 06 '11 at 03:37

3 Answers3

54

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)>
ale
  • 6,369
  • 7
  • 55
  • 65
  • why not just used the built in function query.ColumnList – Matthew Friedman Jun 12 '18 at 17:00
  • @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. – ale Jun 12 '18 at 18:28
4

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.

bpanulla
  • 2,988
  • 1
  • 19
  • 23
  • 3
    Even though this is closed, don't know why, I feel compelled to place this answer, ArrayToList(QueryName["ColumnName"], ","). – Tyler Clendenin Apr 06 '11 at 03:34
  • 1
    Why in the world are you offering these convoluted solutions? What's wrong with ValueList()? – ale Nov 17 '11 at 16:51
  • Treating 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? – bpanulla Nov 26 '11 at 00:48
  • @bpanulla: Fair enough, but the asker did specifically ask for a list. – ale Dec 27 '11 at 15:09
  • 2
    If 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 ;) – bpanulla Dec 27 '11 at 17:36
  • Well, asking "what's the best" anything is a subjective question and likely to be closed, but I get your meaning. – ale Dec 27 '11 at 20:57
  • One 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. – David Faber Mar 06 '13 at 19:31
  • -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. – Jared Beck Apr 30 '13 at 23:00
1

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) />
Dave Babbitt
  • 1,038
  • 11
  • 20