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:
This results in:
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:
Now try this:
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:
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
What can do, however, is to turn our code into a user-defined function.
Paste this code into Query Analyzer and execute it. Test it by passing our example CustomerID into a function call:
This returns, as expected:
10248, 10274, 10295, 10737, 10739
Finally, let’s test it on multiple rows:
Run this line of code and voila! We get all the company names plus a comma-delimited list of the related OrderIDs.
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.