Displaying Rows Grouped by Category
By: Scott Mitchell | Created: 2003-05-13 | Last Updated: 2003-07-25


Imagine that you have a database table named Players with a list of basketball players from the NBA. The schema for this table might look as follows:

Players
FullNamevarchar(50)
Teamvarchar(20)

 

Improving the Data Model...
The schema for the Players table is extremely simple and far from ideal. If this were a "real" database application, we'd likely have a plethora of additional attributes for a player. Also, we'd likely want a PlayerID primary key field to uniquely identify each player (since a player's name is not a guarantee unique attribute). Furthermore, we'd likely have a separate table to store information about each NBA team, and then have the Team attribute in the Players table be a foreign key.

Given this table schema, imagine that we have the following records:

Now, imagine that you wanted to display the NBA players such that the players for each team were grouped together. Clearly, a simple SQL query with an appropriate ORDER BY clause will get all of the players ordered by team. That is, the following query:

SELECT FullName, Team FROM Players ORDER BY Team

Will return all the players playing for the Kings, followed by all the players playing for the Lakers, followed by all the players playing for the Spurs. Specifically, the results of the above SQL query, when run on the sample data, will be as follows:

Therefore, we could have a DataGrid display the name of each player, followed by their team name. However, what if we wanted the display to be a bit different. Rather than blandly list each player and his associated team, we want to list, in big bold letters, the name of the team, followed by all of its players. That is, we want the results to look something like:


Kings
Vlade Divac
Chris Webber
Mike Bibby
Doug Christie

Lakers
Shaq
Rick Fox
Kobe Bryant
Robert Horry

Spurs
Tim Duncan
Terry Parker
David Robinson

Can we get a DataGrid to render this "style" of output? Sure, if we use a TemplateColumn and a bit of clever programming. We could also obtain such an appearance through the use of a master/detail-type DataGrid, which is discussed in detail in An Extensive Examination of the DataGrid Web Control: Part 14.

In using template, we can implement this sort of display through the use of a DataList and a custom function as well. For this FAQ, let's use the DataList approach.

DataGrid or DataList?
Note that a DataList probably makes more sense here since it is designed for templated columns. Furthermore, the DataList allows for repeated columns via its RepeatColumns property, meaning you could display the teams n to a table column. However, if you need functionality like pagination, then the DataGrid would be a better choice.

Using a DataList to Display the Players, Grouped By Team
Imagine, for a moment, that we only want to display the players for each team, and we're not yet worried about displaying the team name before the list of players. To accomplish this, we could use the following simple DataList:

<asp:DataList runat="server" id="dlPlayers"> <ItemTemplate> Container.DataItem("FullName") </ItemTemplate> </asp:DataList>

This DataList will display a table row and column for each player. Now, to display the team name, we need to be able to determine when the team name switches from one team to the next. That is, when we first start by displaying the players for the Kings, we want to emit the team name Kings and then the player's name, but then for each of the remaining players who play for the Kings, we only want to emit their name. When the first player for the next team (the Lakers) is displayed, again, we want to display both the team name and the player's name; however, for the remaining Lakers players we only want to display the player's name.

In the FAQ, Customizing the Appearance of a DataGrid Column Value, we saw how to use a custom function to output a custom value based on the value of a DataSource field. We'll use this technique in this exercise to emit the player's team name if the team name has switched from a previous value to a new value. This can be accomplished with the following code:

<script runat="server" language="VB"> ... Function DisplayTeamIfNeeded(team as String) as String Dim output as String = String.Empty 'Determine if this team has yet to be displayed If team <> lastUsedTeam then 'Set that the lastUsedTeam is the current team value lastUsedTeam = team 'Display the team name output = "<br /><b>" &amp; team &amp; "</b><br />" End If Return output End Function </script> <asp:DataList runat="server" id="dlPlayers"> <ItemTemplate> <%# DisplayTeamIfNeeded(Container.DataItem("Team")) %> <%# Container.DataItem("Player") %> </ItemTemplate> </asp:DataList>
VB.NET

 

<script runat="server" language="C#"> <script runat="server" language="C#"> ... string lastUsedTeam = String.Empty; string DisplayTeamIfNeeded(string team) { string output = String.Empty; // Determine if this team has yet to be displayed if (team != lastUsedTeam) { // Set that the lastUsedTeam is the current team value lastUsedTeam = team; // Display the team name output = "<br /><b>" + team + "</b><br />"; } return output; } </script> <asp:DataList runat="server" id="dlPlayers"> <ItemTemplate> <%# DisplayTeamIfNeeded((string) DataBinder.Eval(Container.DataItem, "Team")) %> <%# DataBinder.Eval(Container.DataItem, "Player") %> </ItemTemplate> </asp:DataList>
C#

 

The following live demo shows this code in action. Note that you can add additional HTML around the output of the team name in the DisplayTeamIfNeeded() function in order to make the team name more pronounced.

An Alternative Method of Grouping Data
For an alternative method of grouping data, be sure to check out Dave Long's article Including Subheadings in a Datagrid.

Home | FAQs | Articles | About | Buy the Book!

Copyright 2006, Scott Mitchell. All Rights Reserved.