Q
Problem solve Get help with specific problems with your technologies, process and projects.

Format query output as header and detail lines

I am using Microsoft Access 2000, and I want to write a query in VB6 using SQL statements to display the department...

name only once, and then all the employee names working under that department. For example:

department A
    employee A1
    employee A2
      ...
    employee An

  
department B
    employee B1
    employee B2
      ...
    employee Bm

This is one of the more frequent SQL questions. The answer is: don't try to do this with SQL only. Formatting is best done in the application program. I don't know VB6, but I'm sure it's completely adequate for this task.

The query is very simple. Just return both "header" and "detail" columns, and make sure you sort the result set by at least the header column:

select deptname
     , emplname
  from yourtable
order
    by deptname
     , emplname

Your result set will look like this:

department A  employee A1
department A  employee A2
   ...
department A  employee An
department B  employee B1
department B  employee B2
    ...
department B  employee Bm

Now process the result set with application code, using "current/previous" logic to determine when to print a heading (this is pseudocode):

set prevdept = ''
while more rows
  begin 
    if dept > prevdept
      print dept header
      set prevdept = dept
    endif
    print empl detail
    get next resultset row
  end

Reeder feedback

Reader Frank Kulash responds:

"Formatting is best done in the application program". That's true, but it's not always possible. Here's one solution for those rare occasions when you have to do the job in SQL.

It's easy to produce all the header lines in one query, and all the detail lines in another query. But how do you combine them in one result set? That's exactly what UNION does.

  SELECT DISTINCT deptname
  FROM    yourtable
UNION
  SELECT  emplname
  FROM    yourtable;
(By the way, this works equally well if the header and detail lines come from different tables.) Okay, now we've got all the right lines, but how do we get them into the right order? Say we want departments sorted alphabetically, then, within each department, the header followed by an alphabetical list of employees. Add sorting columns to the query above.
  SELECT DISTINCT
          deptname  AS sort1
        , 1         AS sort2
        , deptname   AS display_text
  FROM    yourtable
UNION
  SELECT  deptname  AS sort1
        , 2         AS sort2
        , emplname  AS display_text
  FROM    yourtable
ORDER BY  1, 2, 3;
(In this case, there's no need for a sort3 dummy column, since display_text works as well. Department names and employees names won't get mixed together becuase the 1 in sort2 guarantees that the department name appears before any of the employee names.)

Now the only problem is that we've got these ugly sorting columns in the output. We need them for sorting, but we don't want to look at them. Here's one way (at least in Oracle, I don't know about MSAccess) to hide the sort columns.

SELECT  display_text
FROM
(
  SELECT DISTINCT
          deptname  AS sort1
        , 1         AS sort2
        , deptname   AS display_text
  FROM    yourtable
UNION
  SELECT  deptname  AS sort1
        , 2         AS sort2
        , emplname  AS display_text
  FROM    yourtable
)
ORDER BY  sort1
        , sort2
        , display_text;


This was last published in May 2003

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close