how to loop through Query Columns in ColdFusion

Question

I have a query in a my CFC. The function contains a simple query as such.

<cfquery name="qrySE" datasource=#mydatasource#>
SELECT
  NAMES,SALARY
FROM tblTest
</cfquery>

I want to display my resultset as such (horizontally):

NAME1 NAME2 NAME3 NAME4
  10    20    45    62

Is there a way to loop through the columns of my query and create a virtual query for this purpose?

If anyone has done this, please let me know.

Solution 1

Just wanted to add Al Everett's solution returns the columns back in alphabetical order. If you would like to get the column names back in the same order as the query you can use:

ArrayToList( qrySE.getColumnNames() )

which I found here: http://www.richarddavies.us/archives/2009/07/cf_columnlist.php

you can use this to create a function to output queries to a table like this:

<cffunction name="displayQueryAsTable" output="true">   
    <cfargument name="rawQueryObject" type="query" required="true"> 
    <table >
    <tr>
        <cfloop list="#ArrayToList(rawQueryObject.getColumnNames())#" index="col" >
            <th>#col#</th>
        </cfloop>
    </tr>   
    <cfloop query="rawQueryObject">
        <tr>
            <cfloop list="#ArrayToList(rawQueryObject.getColumnNames())#" index="col">
                <td>#rawQueryObject[col][currentrow]#</td>
            </cfloop>
        </tr>   
    </cfloop>
    </table>        
</cffunction>

Solution 2

You could use the built-in query.columnList that is returned with each query. (It's metadata of the query, like recordCount.)

You could do something like this:

<table>
  <cfloop list="#qrySE.columnList#" index="col">
    <tr>
      <cfloop query="qrySE">
        <td>#qrySE[col][currentRow]#</td>
      </cfloop>
    </tr>
  </cfloop>
</table>

Not tested, but that should give you the idea.