Notice: With the launch of Adobe Cookbooks, this site will no longer be accepting new entries or posting new content. Thanks to everyone who submitted content!

How do I output a query result set grouped by a specific field?

To generate this type of display, there are two key things to note in the code sample below. First, the field you wish to group by must appear in the ORDER BY clause of your query and this same field must be used as the "group" attribute for the first <cfoutput> tag in addition to the "query" attribute which tells the tag to loop.

Sample Code:

<cfquery datasource="bluedragon" name="q_getemployees">
SELECT employee.employeefirstname, employee.employeelastname, department.departmentname
FROM employee INNER JOIN department ON employee.departmentid =
department.departmentid
ORDER BY department.departmentname
</cfquery>

<cfoutput query="q_getemployees" group="departmentname">
<h1>#q_getemployees.departmentname#</h1>
<cfoutput>
#q_getemployees.employeefirstname# #q_getemployees.employeelastname#<br />
</cfoutput>
</cfoutput>

Output:

<h1>SAMPLE RESULT:</h1>
<h2>Development</h2>
   Darin Kohles<br />
   Eric Jones<br />
   Colleen Cox<br />
   
<h2>Management</h2>
   Steve Nation<br />
   Ben Wakeman<br />

<h2>Sales</h2>
   David Taylor-Klaus<br />
   Beth Cooper<br />


This question was written by Ben Wakeman.
It was last updated on June 13, 2006 at 2:25:06 PM EDT.

CFML Referenced

<cfquery>
<cfoutput>

Categories

Display and Layout

Comments

Comment made by Lola Lee Beno on June 14, 2006 at 7:53 AM
I'm seeing two closing cfoutput tags, but only one . . . is this correct?

Comment made by Lola Lee Beno on June 14, 2006 at 7:54 AM
Only one opening cfoutput tag, that is . . .

Comment made by Neil Merton on June 14, 2006 at 10:30 AM
There are actually two:

<cfoutput query...

and

<cfoutput>