Grouping ColdFusion Query Results On Multiple Columns
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,
tblConsultantArea.area, tblConsultantRole.roleDescription
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;
width: 450px;
margin-top: 20px;
padding-left: 5px;">
<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 />
<cfoutput group="area">
<!---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 />
<cfoutput>
<!---for each role that is different for the current area do the following--->
<span style="margin-left: 30px;
font-size: .8em;
color: #333333;">Role: #roleDescription#</span><br />
</cfoutput> <!---end inner role group--->
<br />
</cfoutput> <!---end area group--->
</p>
</div>
</cfoutput><!---end Last_Name group--->
The result of running the above code is:
All Consultants
Consultant: Inis Smith, MD, FAAFP
[email protected]
Aurora, CO United States
Area: Developing collaborations
Role: serve as a resource
Area: Educational research
Role: review a grant or a manuscript
Role: serve as a grant consultant
Role: serve as a project co-investigator
Role: serve as a resource
Role: teach
Area: Ethical issues
Role: serve as a resource
Area: Instrument development/validation
Role: review a grant or a manuscript
Role: serve as a grant consultant
Role: serve as a project co-investigator
Role: serve as a resource
Role: teach
Area: Measuring outcomes
Role: review a grant or a manuscript
Role: serve as a grant consultant
Role: serve as a project co-investigator
Role: serve as a resource
Role: teach
Area: Multi-method research
Role: serve as a resource
Area: Poster presentations
Role: review a grant or a manuscript
Role: serve as a grant consultant
Role: serve as a project co-investigator
Role: serve as a resource
Role: teach
Area: Project management
Role: serve as a resource
Area: Research presentations
Role: review a grant or a manuscript
Role: serve as a grant consultant
Role: serve as a project co-investigator
Role: serve as a resource
Role: teach
Area: Writing skills
Role: review a grant or a manuscript
Role: serve as a grant consultant
Role: serve as a project co-investigator
Role: serve as a resource
Role: teach
Consultant: Beth Smith, MD
[email protected]
Baltimore, MD United States
Area: cohort designs
Role: review a grant or a manuscript
Role: serve as a grant consultant
Role: serve as a project co-investigator
Role: serve as a resource
Area: Randomized controlled trials
Role: review a grant or a manuscript
Role: serve as a grant consultant
Role: serve as a project co-investigator
Role: serve as a resource
Area: Researching special populations
Role: review a grant or a manuscript
Role: serve as a grant consultant
Role: serve as a project co-investigator
Role: serve as a resource
