Unusual - Removing repetition from queries

From: David Smith <davidsmith633_at_hotmail.com>
Date: 17 Feb 2003 15:32:26 -0800
Message-ID: <d54a9dfe.0302171532.5c0dbe_at_posting.google.com>

Hello there,

I wonder if anyone can point me in the right direction here.

I want to write a query that lists a whole lot of items that each belong to a group and a sub-group. However, some of the group and sub-group names are really long, which make the query results hard to follow.

So what I want to do is stop repeating myself. Rather than list the items as shown in table 1 below, I'd like to move the repeating fields into a header and do something like what is shown in table 2.

This effect becomes more apparent with larger quantities of data than I am showing here.

Is there a way to do this in standard SQL without resorting to writing a stored procedure?

Thanks,
David.

TABLE 1: TOO MUCH REPETITION Group No Group Name SubGroup No SubGroup Name Item

---

   1      First Group     5         First SubGroup   First Item
   1      First Group     5         First SubGroup   Second Item
   1      First Group     5         First SubGroup   Third Item
   1      First Group     5         First SubGroup   Fourth Item
   1      First Group     5         Second SubGroup  Fifth Item
   1      First Group     6         Second SubGroup  Sixth Item
   1      First Group     6         Second SubGroup  Seventh Item
   1      First Group     6         Second SubGroup  Eigth Item
   1      First Group     6         Second SubGroup  Ninth Item
   1      First Group     6         Second SubGroup  Tenth Item
   2      Second Group    7         Third SubGroup   Item 11
   2      Second Group    7         Third SubGroup   Item 12
   2      Second Group    7         Third SubGroup   Item 13
   2      Second Group    7         Third SubGroup   Item 14
   2      Second Group    7         Third SubGroup   Item 15
   2      Second Group    8         Fourth SubGroup  Item 16
   2      Second Group    8         Fourth SubGroup  Item 17
   2      Second Group    8         Fourth SubGroup  Item 18
   2      Second Group    8         Fourth SubGroup  Item 19
   2      Second Group    8         Fourth SubGroup  Item 20

TABLE 2: EASIER TO READ Group No 1: First Group - SubGroup No 5: First SubGroup

---

First Item
Second Item
Third Item
Fourth Item
Fifth Item

Group No 1: First Group - SubGroup No 6: Second SubGroup

---

Sixth Item
Seventh Item
Eigth Item
Ninth Item
Tenth Item

Group No 2: Second Group - SubGroup No 7: Third SubGroup

---

Item 11
Item 12
Item 13
Item 14
Item 15

Group No 2: Second Group - SubGroup No 8: Fourth SubGroup

---

Item 16
Item 17
Item 18
Item 19
Item 20 Received on Tue Feb 18 2003 - 00:32:26 CET