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

2Jan/090

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

Google AdSense

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  

kick it on DotNetKicks.com

James Welch

James Welch is a software engineer in Vermont working for a large information technology company and specializing in .NET. Additionally, he holds a Master’s Degree in Software Engineering and a Bachelor of Science Degree in Computer Science. Jim also enjoys local craft beer, comic books, and science-fiction and fantasy novels, games, and movies.

Twitter Google+ 

Comments (0) Trackbacks (0)

No comments yet.


Leave a Reply

No trackbacks yet.