ColdFusion - Output Grouping From a Query

Question

I have a great question that is probably bone-head simple. I have the following query:

<cfquery name="getempareview" dbtype="query">
  SELECT firstname,lastname,deptname,supcode
  FROM getreviews
  WHERE supcode IN (#preserveSingleQuotes(setsupcode)#)
</cfquery>

What I need to do is output so that the supcode has the other data under it in a list. So, if I have 100 rows of data and the supcode is the same on 25 of the records, just have the following:

supcode firstname lastname - deptname (all 25 records would be listed out here)

Any help would be greatly appreciated.

Answer 1

Nested outputs. Try this.

<cfoutput query="YourQueryName" group="SupCode">
<h2>#SupCode#</h2>
<cfoutput>
#FirstName# #LastName# <br/>
</cfoutput>

</cfoutput>

Answer 2

You need to use nested and grouped output. And add an ORDER BY to your query.

<cfset setsupcode = "1,3,5">

<cfquery name="getempareview" dbtype="query">
  SELECT firstname,lastname,deptname,supcode
  FROM getreviews
  WHERE supcode IN (<cfqueryparam value="#setsupcode#" cfsqltype="numeric" list="yes">)
  ORDER BY supcode, deptname, lastname, firstname
</cfquery>

<cfoutput query="getempareview" group="supcode">
    <h2>#supcode#</h2>
    <cfoutput group="deptname">
        #firstname# #lastname# (#deptname#) <br>
    </cfoutput>
</cfoutput>

https://trycf.com/gist/763ede5485b0978504250f7f5baf9deb/acf11?theme=solarized_dark

Also, since this is apparently a Query of Query, you may be able to better organize your data in your initial query, rather than having to come back to reprocess the data.