I needed to display the results of a ColdFusion query grouped by the person's last name, then grouped by the person's areas of experties, and then by the roles for each area. I knew that the group attribute of the cfoutput tag allowed you to group the output (see ColdFusion 7 Online Documentation), but I thought--incorrectly-- that you could only group on one of the query columns.
After reading through the discussion about "Grouping Result Output" in ColdFusion MX Web Application Construction Kit (5th Edition, pages 227-231), I found a tip that stated "...but there is no limit to the number of levels in which data can be grouped." The key is that the SQL statement must sort the result in the order you want to group. The ability to group my query result output on multiple levels was exactly what I needed.
To do a quick test. I created the following query (note I did not create these tables):
SELECT Main.First_Name, Main.Last_Name, Main.Title2, Main.city, Main.state, Main.country, Main.Email,
FROM ((tblConsultantToAreaToRole INNER JOIN tblConsultantArea ON tblConsultantToAreaToRole.areaID = tblConsultantArea.areaID)
INNER JOIN tblConsultantRole ON tblConsultantToAreaToRole.roleID = tblConsultantRole.roleID)
INNER JOIN Main ON tblConsultantToAreaToRole.personID = Main.[Person ID]
ORDER BY Main.Last_Name, tblConsultantArea.area, tblConsultantRole.roleDescription;
Looking at the above you will see that I sorted on the same three columns I want to group: Last_Name, area, and roleDescription.
To output the query results grouped by these three columns, I use nested cfoutput tags with the column name as the value for the group attribute. The first cfoutput tag's group attribute has the value of the first column I sorted the query results on, the second cfoutput tag's group attribute has the value fo the second column I sorted on, and the last inner cfoutput tag doesn't need a group attribute since I want to display each roleDescrption value for the current area.
<cfoutput query="allQry" group="Last_Name">
<!---for each Last_Name that is different do the following--->
<div style="background-color: #FFFFCC;
border: thin solid #990000;
<p>Consultant: <b>#first_name# #last_name#, #title2#</b><br />
<span style="margin-left: 15px;"><a href="mailto:#email#">#email#</a></span><br />
<span style="margin-left: 15px;">#city#, #state# #country#</span><br><br />
<!---for each area that is different for the current Last_Name do the following--->
<span style="margin-left: 15px;
color: #000099;">Area: #area# </span><br />
<!---for each role that is different for the current area do the following--->
<span style="margin-left: 30px;
color: #333333;">Role: #roleDescription#</span><br />
</cfoutput> <!---end inner role group--->
</cfoutput> <!---end area group--->
</cfoutput><!---end Last_Name group--->
The result of running the above code is: