CFLib.org – Common Function Library Project

RowsToColumns(query, maxcolumns, actualColumnCountVarName)

Last updated March 10, 2010

author

Nathan Dintenfass

Version: 1 | Requires: CF5 | Library: DataManipulationLib

Description:
Takes a query and transforms it to allow outputting for viewing in columns instead of rows. You specify the query you want to parse and the number of columns to put it into. For example, if you have output that looks like: <PRE> a b c d e f </PRE> you could output it as <PRE> a c e b d f </PRE> PLEASE NOTE: This function will put empty strings into cells that do not need filling, so if you have: <PRE> a d g b e h c f </PRE> The cell after "f" would contain empty values. This is done to allow consistent and easy outputting without needing to a lot of parsing after the tag is called. It is still up to you as a developer to do the work to put the output into the proper number of columns. For instance: <PRE> <cfset getUsers = rowsToColumns(yourQuery,4,"cols")> <table border="1"> <tr> <cfset counter = 0> <cfoutput query="GetUsers"> <cfset counter = counter + 1> <td>#LastName#, #FirstName#</td> <cfif counter is cols and counter is not getUsers.recordcount> </tr><tr> <cfset counter = 0> </cfif> </cfoutput> </tr> </table> </PRE>

Return Values:
Returns a query.

Example:

<CFSCRIPT>
	Q = QueryNew("Name");
	QueryAddRow(Q,11);
	QuerySetCell(Q,"Name","Anna",1);
	QuerySetCell(Q,"Name","Barry",2);
	QuerySetCell(Q,"Name","Charles",3);
	QuerySetCell(Q,"Name","Dana",4);
	QuerySetCell(Q,"Name","Ebert",5);
	QuerySetCell(Q,"Name","Fred",6);
	QuerySetCell(Q,"Name","Gorf",7);
	QuerySetCell(Q,"Name","Jeanne",8);
	QuerySetCell(Q,"Name","Hank",9);
	QuerySetCell(Q,"Name","Ingrid",10);
	QuerySetCell(Q,"Name","Jacob",11);
</CFSCRIPT>

 <cfset new = rowsToColumns(Q,3,"cols")>
 <table border="1">
 	<tr>
 		<cfset counter = 0>
 		<cfoutput query="new">
 			<cfset counter = counter + 1>
 			<td>#name#</td>
 			<cfif Counter is cols and counter is not new.recordcount>
 				</tr><tr>
 				<cfset counter = 0>
 			</cfif>
 		</cfoutput>
 	</tr>
 </table>

Parameters:

Name Description Required
query A ColdFusion query. Yes
maxcolumns The maximum number of columns. Yes
actualColumnCountVarName The name of the variable to set containing the actual number of columns created. Yes

Full UDF Source:

/**
 * Transforms queries for displaying as columns instead of rows.
 * This UDF is based on the custom tag CF_RowsToColumns created by Nathan Dintenfass and Ben Archibald in February, 2000
 * 
 * @param query 	 A ColdFusion query. (Required)
 * @param maxcolumns 	 The maximum number of columns. (Required)
 * @param actualColumnCountVarName 	 The name of the variable to set containing the actual number of columns created. (Required)
 * @return Returns a query. 
 * @author Nathan Dintenfass (nathan@changemedia.com) 
 * @version 1, March 10, 2010 
 */
function rowsToColumns(query,maxColumns,actualColumnCountVarName){
	//make an array of the columns in the incoming query for looping
	var columnArray = listToArray(query.columnlist);
	//make a new query to return based on the columns of the incoming query
	var newQuery = queryNew(query.columnlist);
	//figure out how many rows there will be
	var rows = ceiling(query.recordcount/maxColumns);
	//set up a var to count row we are on
	var onRow = 1;
	//set up a var to count the column we are on
	var onColumn = 0;
	//set up a var to hold the row we want to grab
	var getRow = 0;
	//set up a var to index the outer loop
	var ii = 1;
	//set up a var to index the inner loop
	var zz = 1;
	//if there will be extra columns, make sure no more columns than necessary.  this is necessary to ensure that if you ask for more columns than there are records to fill you know how many there really are!!
	if(ceiling(query.recordcount/rows) LT maxColumns)
		maxColumns = ceiling(query.recordcount/rows);
	//starting on row 1, loop through the recordcount of the original query, putting rows in the new query					
	for(ii = 1; ii lte evaluate(rows * maxColumns); ii = ii + 1){
		//increment the column we are now on
		onColumn = onColumn + 1;
		//get the proper row from the original query
		getRow = ((onColumn - 1) * rows) + onRow;
		//now add a row to the newQuery
		queryAddRow(newQuery);
		//loop through the columns, putting the cells into the newQuery
		for(zz = 1; zz lte arraylen(columnArray); zz = zz + 1){
			//if the row we want is lower than than the recordcount, put in the value
			if(getRow LTE query.recordcount)
				querySetCell(newQuery,columnArray[zz],query[columnArray[zz]][getRow]);
			//otherwise, just set it to a blank string
			else
				querySetCell(newQuery,columnArray[zz],"");
		} 
		//if the column we are on is the same as the maxColumns, reset the column we are on and increment the row
		if(onColumn EQ maxColumns){
			onColumn = 0;
			onRow = onRow + 1;
		}
	}
	//set the variable for the number of columns!
	setVariable(actualColumnCountVarName,maxColumns);
	//return the new query
	return newQuery;				
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson