Using COALESCE to Build Comma-Delimited String

By Garth Wells on 1 February 2001 | Tags: Application Design

Garth is back with another article. This one talks about building a comma-separated value string for use in HTML SELECT tags. It's also handy anytime you need to turn multiple records into a CSV field. It's a little longer and has some HTML but a good read.

I was reading the newsgroups a couple of days ago and came across a solution posted by Itzik Ben-Gan I thought was really smart. In order for you to understand why I like it so much I have to give you a little background on the type of applications I work on. Most of my projects for the past 2.5 years have focused on developing browser-based applications that access data stored in SQL Server. On almost all the projects I have worked on there has been at least one Add/Edit screen that contained a multi-select list box.

For those of you with limited experience working with HTML, I need to explain that the values selected in a multi-select list box are concatenated in a comma-delimited string. The following HTML creates a multi-select list box that displays retail categories.

<SELECT name="RetailCategory" multiple>
 <OPTION value=1>Shoes
 <OPTION value=2>Sporting Goods
 <OPTION value=3>Restaurant
 <OPTION value=4>Women's Clothes
 <OPTION value=5>Toys
</SELECT>

If a user selects more than one option the value associated with RetailCategory is a comma-delimited string. For example, if the user selects Shoes, Women's Clothes and Toys, the value associate with RetailCategory is 1, 4, 5. When the user Submits their form I call a stored procedure to insert the data into the appropriate tables. The comma-delimited string is processed with a WHILE loop and the individual values are inserted into a dependent table.

Now that we have covered the Add part, let's take a look at what happens when a user wants to Edit a row. When editing a row, you need to populate the form with the existing values--this includes making sure all the multi-select list box values that are associated with the row are shown as selected. To show an option as selected, you use the "selected" attribute. If we were editing the row associated with the previous example the final HTML would look like the code shown here.

<SELECT name="RetailCategory" multiple>
 <OPTION value=1 selected>Shoes
 <OPTION value=2>Sporting Goods
 <OPTION value=3>Restaurant
 <OPTION value=4 selected>Women's Clothes
 <OPTION value=5 selected>Toys
</SELECT>

I say final, because the actual HTML is built on-the-fly using VBScript. To determine which options are shown as selected, you must return a comma-delimited string to IIS so you can manipulate it with VBScript. I use the Split function and a For loop to determine which options should be shown as selected. The following VBScript shows how this is done.

<%
EmpArray = Split(rs("EmployeeList"))
For Each i In EmpArray
  If rs2("Emp_UniqueID") = CInt(i) Then
    response.write "selected"
  End If
Next
%>

The remainder of this article shows the inefficient way I used to build the string along with the new, efficient way I learned from the newsgroup posting.

The Old, Inefficient Approach

Let's create and populate some tables so we have some data to work with. Assume you have a sales effort management (SEM) system that allows you to track the number of sales calls made on a potential client. A sales call is not a phone call, but a get together such as lunch or another type of person-to-person meeting. One of the things the VP of Sales wants to know is how many of his sales personnel participate in a call. The following tables allow you to track this information.

CREATE TABLE Employees
(
Emp_UniqueID smallint PRIMARY KEY,
Emp_FName varchar(30) NOT NULL,
Emp_LName varchar(30) NOT NULL,
)
go

CREATE TABLE SalesCalls
(
SalCal_UniqueID smallint PRIMARY KEY,
SalCal_Desc varchar(100) NOT NULL,
SalCal_Date smalldatetime NOT NULL,
)
go

CREATE TABLE SalesCallsEmployees
(
SalCal_UniqueID smallint NOT NULL,
Emp_UniqueID smallint NOT NULL,
)
go

A limited number of columns are used in order to make this article easier to digest. The SalesCallsEmployees table is a junction table (aka associative table) that relates the employees (sales personnel) to a particular sales call. Let's populate the tables with sample data using these INSERT statements.

INSERT Employees VALUES (1,'Jeff','Bagwell')
INSERT Employees VALUES (2,'Jose','Lima')
INSERT Employees VALUES (3,'Chris','Truby')
INSERT Employees VALUES (4,'Craig','Biggio')

INSERT SalesCalls VALUES (1,'Lunch w/ John Smith','01/21/01')
INSERT SalesCalls VALUES (2,'Golfing w/ Harry White','01/22/01')

INSERT SalesCallsEmployees VALUES (1,1)
INSERT SalesCallsEmployees VALUES (1,2)
INSERT SalesCallsEmployees VALUES (1,4)
INSERT SalesCallsEmployees VALUES (2,2)

The first sales call (Lunch w/ John Smith) had three employees participate. Using the old approach, I used the code shown here (inside a stored procedure) to build the comma-delimited string. The resultset shows the output when the "Lunch w/ John Smith" sales call is edited.

DECLARE @Emp_UniqueID int,
@EmployeeList varchar(100)

SET @EmployeeList = ''

DECLARE crs_Employees CURSOR
FOR SELECT Emp_UniqueID
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

OPEN crs_Employees
FETCH NEXT FROM crs_Employees INTO @Emp_UniqueID

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @EmployeeList = @EmployeeList+CAST(@Emp_UniqueID AS varchar(5))+ ', '
  FETCH NEXT FROM crs_Employees INTO @Emp_UniqueID
END

SET @EmployeeList = SUBSTRING(@EmployeeList,1,DATALENGTH(@EmployeeList)-2)

CLOSE crs_Employees
DEALLOCATE crs_Employees

SELECT @EmployeeList

--Results--

---------
1, 2, 4

This code may look a little complicated, but all it's doing is creating a cursor that holds the Emp_UniqueID values associated with the sales call and processing it with a WHILE to build the string. The important thing for you to note is that this approach takes several lines of code and uses a cursor. In general, cursors are considered evil and should only be used as a last resort.

The New and Improved Approach

The new and improved approach can create the same resultset with a single SELECT statement. The following code shows how it's done.

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

--Results--

---------
1, 2, 4

The COALESCE function performs the magic here. When @EmployeeList is NULL (the first row processed), it returns an empty string. On subsequent rows, it concatenates the @EmployeeList value with a comma and the current @Emp_UniqueID value.