Comma-delimited lists

from the Artful SQL Server & Access Tips List

The Problem Suppose that you are required to deliver a comma-delimited list whose values consist of a particular column from a table. To take a simple example, create a list of the orders placed by a specified customer. Using the Northwind database (installed with SQL Server, we might specify the customer whose CustomerID is ‘VINET’. How do you visit multiple rows within a single SELECT, while also assembling their values into a string? As far as I know, you cannot do it. What you can do, however, is create a stored procedure that creates a variable and relies on Coalesce() to assemble the list for you. If you’ve never used Coalesce(), a word of explanation is in order. This function accepts a list of values and returns the first that is not null. Open Query Analyzer, select the Northwind database, and paste this code in: 
DECLARE @OrderList varchar(100)
SELECT @OrderList = COALESCE(@OrderList + ', ', '') +
CAST(OrderID AS varchar(500))
FROM Orders
WHERE CustomerID = 'VINET'
SELECT @OrderList
This results in: 
10248, 10274, 10295, 10737, 10739
We can now extend this to include multiple tables. For example, suppose we want the company name from the Customers table as well as the list of orders. Do this: 
DECLARE @OrderList varchar(100)
SELECT @OrderList = COALESCE(@OrderList + ', ', '') +
CAST(OrderID AS varchar(500))
FROM Orders
WHERE CustomerID = 'VINET'
Now try this: 
SELECT Customers.CompanyName, @OrderList
FROM Customers
WHERE CustomerID = 'VINET'
The Complications The obvious problem thus far is that the CustomerID is hard-wired, but we fix that quite easily by turning our SQL into a stored procedure that declares a parameter @CustomerID, and then pass in the value of interest. This works, as far as it goes, but unfortunately it does not go far. If we change the code slightly in an attempt to get multiple rows, sadly we find that we obtain the same list of orders for all customers. Enter this in Query Analyzer: 
Alter Procedure ap_OrderList3
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
As
/* set nocount on */
DECLARE @OrderList varchar(500) -- who knows how many orders there may be? Assume about 100
SELECT @OrderList = COALESCE(@OrderList + ', ', '') +
CAST(OrderID AS varchar(5))
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
SELECT Customers.CompanyName, @OrderList
FROM Customers
RETURN
Should we need to list multiple customers with multiple order lists, our only solution thus far is to call our stored procedure multiple times. Assuming that we’re feeding the results into Crystal Reports or Word or some other front end, this is unacceptable. How do we get around this? There are a couple of complications in this approach. Most obviously, it hard-wires the particular customer into the solution. That part is easily fixed by adding a parameter and passing in the CustomerID of interest. However, we can’t combine this The Solution What can do, however, is to turn our code into a user-defined function. 
CREATE FUNCTION dbo.fn_OrderLIst
(
@CustomerID Varchar(5)
)
RETURNS Varchar(100)
AS
BEGIN
DECLARE @OrderList varchar(100)
SELECT @OrderList = COALESCE(@OrderList + ', ', '') +
CAST(OrderID AS varchar(5))
FROM Orders
WHERE CustomerID = @CustomerID
RETURN @OrderList
END
Paste this code into Query Analyzer and execute it. Test it by passing our example CustomerID into a function call: 
SELECT dbo.fn_OrderList('VINET')
This returns, as expected: 10248, 10274, 10295, 10737, 10739 Finally, let’s test it on multiple rows: 
SELECT Customers.CompanyName, dbo.fn_OrderList(Customers.CustomerID) 
FROM Customers
Run this line of code and voila! We get all the company names plus a comma-delimited list of the related OrderIDs. Summary Using this technique, you can easily create lists of values from multiple rows of any table. As the example illustrates, most often you would apply this to rows related to some parent row. This requires creating a user-defined function that assembles the data you need, but using a function enables you to retrieve the desired data from multiple rows of interest, since you can apply a WHERE clause to your SELECT statement.

Last updated 22 May 2024
Return to the Artful SQL Server & Access Tips page