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.