– Common Function Library Project

qryOrderToTable(theQuery[, tableCols])

Last updated March 07, 2010


Jose Alberto Guerra

Version: 0 | Requires: CF5 | Library: DatabaseLib

When filling an HTML table with a query result, it will display the query horizontally (cell, cell, next_row, cell cell ...), this UDF will change the order of the query to display vertically (row, row, row, next_column, row, row ...)

Return Values:
Returns a query.


<cfset tempQry=QueryNew("x")>
<cfset temp = QueryAddRow(tempQry, 60)>
<cfloop index="i" from="1" to="60"><cfset temp = QuerySetCell(tempQry, "x", i, i)></cfloop>
<cfset qryNew=qryOrderToTable(tempQry, 4)>
    <table border><cfset cont=0>
        <tr><cfloop query="qryNew"><cfset cont=cont+1><td>#x#</td><cfif cont eq 4></tr><tr><cfset cont=0></cfif></cfloop></tr>


Name Description Required
theQuery cfquery object to "rotate" Yes
tableCols Number of columns to output No

Full UDF Source:

 Change query order from horizontal to vertical to display in a HTML table
 @param theQuery      cfquery object to "rotate" (Required)
 @param tableCols      Number of columns to output (Optional)
 @return Returns a query. 
 @author Jose Alberto Guerra ( 
 @version 0, March 6, 2010 
<cffunction name="qryOrderToTable" returntype="query" hint="Changes horizontal order to vertical in a query to display in a HTML table" output="no">
    <cfargument name="theQuery" type="query" required="yes">
    <cfargument name="tableCols" type="numeric" default="2">
    <cfset var change=ceiling(theQuery.recordCount/tableCols)>
    <cfset var column=0>
    <cfset var newQry=QueryNew("#theQuery.columnList#")>
    <cfset var temp = QueryAddRow(newQry, theQuery.recordCount)>
    <cfset var thisRow=0>
    <cfset var c=0>
    <cfset var newPos=0>
    <cfset var thisCol=0>

    <cfoutput query="theQuery">
        <cfset thisRow=currentRow>
        <cfloop index="c" from="1" to="#tableCols#">
            <cfif currentRow gt (change*c)><cfset column=c><cfset thisRow=currentRow-(change*c)></cfif>
        <cfset newPos=thisRow+column+((thisRow-1)*(tableCols-1))>
        <cfloop index="thisCol" list="#theQuery.columnList#">
            <cfset temp = QuerySetCell(newQry, thisCol, evaluate(thisCol), newPos)>
    <cfreturn newQry>


Latest Additions

Raymond Camden added
November 04, 2017

Leigh added
May 11, 2016

Raymond Camden added
May 10, 2016

Kevin Cotton added
May 05, 2016

Raymond Camden added
April 25, 2016

Created by Raymond Camden / Design by Justin Johnson