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
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;