Jim's Blog Ramblings about novels, comics, programming, and other geek topics

How to create a delimited list of values from a one-to-many relationship

Recently, I needed to denormalize a database by putting child records into a single delimited field. For example, the following tables contain source data and the expected output from our function.

The following code is based on Microsoft SQL Server 2005 and has only been tested using Microsoft SQL Server 2005.

Data Tables

Person table

PersonID	FirstName	LastName
1	John	Doe
2	Jane	Doe
3	Bob	Smith
4	Frank	Smith

FavoriteFood table

FavoriteFoodID	PersonID	FoodDescription
1	1	Pizza
2	1	Hot Dog
3	1	Cheeseburger
4	2	Pizza
5	2	Peanut Butter & Jelly Sandwich
6	3	Coffee

Expected Output

John should have 3 food items displayed.

Jane should have 2 food items displayed.

Bob should have 1 food item displayed.

Frank should have 0 food items displayed.

PersonID	FirstName	LastName	FoodList
1	John	Doe	Pizza, Hot Dog, Cheeseburger
2	Jane	Doe	Pizza, Peanut Butter & Jelly Sandwich
3	Bob	Smith	Coffee
4	Frank	Smith

As you see in the FoodList field, the child records have been combined into a single comma delimited field.

Writing the SQL Scalar-valued Function

The SQL function is outlined below. The key elements are the name of the function and the input argument. This must be the foreign key of the child table that relates to your top level data. The delimiter (comma in this example) is inserted in the COALESCE statement below, so if you needed to change it to another delimiter, just replace the comma.

CREATE FUNCTION [dbo].[GetFoodList] 
(
    @PersonID int
)
RETURNS varchar(1000)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar(1000)

    -- Add the T-SQL statements to compute the return value here
    SELECT @Result = COALESCE(@Result + ', ', '') + f.FoodDescription
    FROM FavoriteFood f
    WHERE f.PersonID = @PersonID

    -- Return the result of the function
    RETURN @Result

END

Creating Your SQL View

Finally, you can create a view or just execute your SQL statements. Below is the SQL syntax to create a few by calling the function above.

CREATE VIEW [dbo].[Person_FoodList] AS
SELECT p.PersonID, p.FirstName, p.LastName, 
       GetFoodList(p.PersonID) as FoodList
FROM Person p

The output of the view will look like our expected output table.

Select SQL Statement

SELECT *
FROM Person_FoodList

Output

PersonID	FirstName	LastName	FoodList
1	John	Doe	Pizza, Hot Dog, Cheeseburger
2	Jane	Doe	Pizza, Peanut Butter & Jelly Sandwich
3	Bob	Smith	Coffee
4	Frank	Smith