Forum OpenACS Q&A: Report Groupings from SQL in TCL

Collapse
Posted by Bob OConnor on

Is there a standard way to get headings from an SQL Query to make groups appear properly? Here is the raw data that has been ordered and spit out of the database:

Maine, Augusta, Alpha
Maine, Augusta, Beta
Maine, China,  Allen
Maine, China,  Zoey
New York, Buffalo, Alice
New York, Buffalo, Zed

I want to have a nice report that prints with State and City headers thus

Maine
   Augusta
       Alpha
       Beta
   China
       Allen
       Zoey
New York
   Buffalo
       Alice
       Zed

In the past with foxpro I used some nice "banding" report writers. ... With tcl/sql I've done some messy/kludgy coding to have group names, like the State and City to print only once.
** So my question is, what is a good efficient and elegant way to do reporting.
-Bob