– Common Function Library Project

QueryTranspose(inputQuery[, includeHeaders])

Last updated August 24, 2005


Glenn Buteau

Version: 1 | Requires: CF6 | Library: DataManipulationLib

Transpose a 2D query, turning rows into columns and columns into rows. Original column headers can be turned on or off.

Return Values:
Returns a query.


<cfset myQuery = QueryTranspose(myQuery)>


Name Description Required
inputQuery The query to transpose. Yes
includeHeaders Determines if headers should be included as a column. Defaults to true. No

Full UDF Source:

 Transpose a query.
 @param inputQuery      The query to transpose. (Required)
 @param includeHeaders      Determines if headers should be included as a column. Defaults to true. (Optional)
 @return Returns a query. 
 @author Glenn Buteau ( 
 @version 1, August 24, 2005 
<cffunction name="queryTranspose" returntype="query">
    <cfargument name="inputQuery" type="query" required="true">
    <cfargument name="includeHeaders" type="boolean" default="true" required="false">
    <cfset var outputQuery = QueryNew("")>
    <cfset var columnsList = inputQuery.ColumnList>
    <cfset var newColumn = ArrayNew(1)>
    <cfset var row = 1>
    <cfset var zeroString = "000000">
    <cfset var padFactor = int(log10(inputQuery.recordcount)) + 1 >
    <cfset var i = "">
    <cfif includeHeaders>
        <cfset queryAddColumn(OutputQuery,"col_#right(zeroString & row, padFactor)#",listToArray(ColumnsList))>
        <cfset row = row + 1>

    <cfloop query="inputQuery">
        <cfloop index="i" from="1" to="#listlen(columnsList)#">
            <cfset newColumn[i] = inputQuery[ListGetAt(columnsList, i)][currentRow]>
        <cfset queryAddColumn(outputQuery,"col_#right(zeroString & row, padFactor)#",newColumn)>
        <cfset row = row + 1>
    <cfreturn outputQuery>


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