Edit

Share via

Collapse Groups of Rows (Visual Database Tools)

Applies to: SQL Server

You can create a query result in which each result row corresponds to an entire group of rows from the original data. When collapsing rows, there are several things to keep in mind:

  • You can eliminate duplicate rows Some queries can create result sets in which multiple identical rows appear. For example, you can create a result set in which each row contains the city and state name of a city containing an author - but if a city contains several authors, there are several identical rows. The resulting SQL might look like this:

    SELECT city,
       state
FROM authors;

    The result set generated by the preceding query isn't very useful. If a city contains four authors, the result set includes four identical rows. Since the result set doesn't include any columns other than city and state, there's no way to distinguish the identical rows from each other. One way to avoid such duplicate rows is to include additional columns that can make the rows different. For example, if you include author name, each row is different (provided no two like-named authors live within any one city). The resulting SQL might look like this:

    SELECT city,
       state,
       fname,
       minit,
       lname
FROM authors;

    Of course, the preceding query eliminates the symptom, but doesn't really solve the problem. That is, the result set has no duplicates, but it's no longer a result set about cities. To eliminate duplicates in the original result set, and still have each row describe a city, you can create a query returning only distinct rows. The resulting SQL might look like this:

    SELECT DISTINCT city,
                state
FROM authors;

    For details about eliminating duplicates, see Exclude duplicate rows.

  • You can calculate on groups of rows That is, you can summarize information in groups of rows. For example, you can create a result set in which each row contains the city and state name of a city containing an author, plus a count of the number of authors contained in that city. The resulting SQL might look like this:

    SELECT city,
       state,
       COUNT(*)
FROM authors
GROUP BY city, state;

    For details about calculating on groups of rows, see Summarize query results and Sort and group query results.

  • You can use selection criteria to include groups of rows For example, you can create a result set in which each row contains the city and state name of a city containing several authors, plus a count of the number of authors contained in that city. The resulting SQL might look like this:

    SELECT city,
       state,
       COUNT(*)
FROM authors
GROUP BY city, state
HAVING COUNT(*) > 1;

    For details about applying selection criteria on groups of rows, see Specify conditions for groups and Use HAVING and WHERE clauses in the same query.

Related content