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;
