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.
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>
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.
