« Zamzar - Free online file conversion | IE Developer Toolbar Beta 3 install hangs »

March 9, 2007

Cfoutput group tip

Yesterday I experienced a problem with my first attempt to use the group attribute of the cfoutput tag. I was trying to use multiple nested cfoutput tags with the group attribute to create a table whose rows were grouped together by one column of a query. Within those groups, I wanted to group the rows by a second column value.

The problem I was having was that ColdFusion wasn’t grouping my 2nd level rows like it should. I assumed that cfoutput sorted the query appropriately in order to perform the necessary groupings. What I failed to realize was that you have to manually sort the query with the SQL order by clause.

In other words, any column used in the a cfoutput group attribute must be in the SQL order by clause (in the order that the cfoutput tags are nested).

Here’s a code sample:

<cfquery name="q">
	SELECT Year,Month,Col1,Col2,Col3
	FROM table
	ORDER BY Year,Month
</cfquery>

<cfoutput query="q" group="Year">
	<h3>#Year#</h3>
	<cfoutput group="Month">
		<table border="1">
		<tr>
			<td colspan="3">#Month#</td>
		</tr>
		<tr>
			<td>Col1</td>
			<td>Col2</td>
			<td>col3</td>
		</tr>
		<cfoutput>
			<tr>
				<td>#Col1#</td>
				<td>#Col2#</td>
				<td>#Col3#</td>
			</tr>
		</cfoutput>
		</table>
	</cfoutput>
</cfoutput>

Posted at 10:51 AM in ColdFusion

Comments

1. Tom says:

Richard, thanks for this little gem. I've been banging my head against the wall for the last 2 hours wondering why my table wasn't coming out properly. VERY important to make sure the query is ordered by the column that you're going to group by - and they don't seem to tell you that in the docs.

Posted on April 4, 2010 at 1:18 AM

2. jyoseph says:

Agreed w/ Tom, this is a gem. Something you "know" but sometimes forget. Thanks for the reminder!

Posted on November 19, 2010 at 10:23 AM

3. Bonnie says:

THANK YOU! I too was sitting here for an hour over complicating this issue.

Posted on June 10, 2011 at 3:29 AM