How to Create Delimited Lists of Related Data in Your MS SQL Queries

A project I’m working on requires me to return asset records along with a comma-delimited list of related keywords. The keywords are stored in their own table and there is a one-to-many relationship from assets to keywords.

The following code returns what I’m looking for, but only for one record:


DECLARE @KeywordList varchar(1000)

SELECT @KeywordList = COALESCE(@KeywordList + ', ', '') + k.keyword
FROM assets a
JOIN keywords k ON a.asset_guid = k.asset_guid
WHERE a.asset_guid = 'xxxxxxxx-xxxx-xxxx-xxxx-023ECD4EBB4C'

SELECT @KeywordList

The problem was that I needed to return the results in a column for every record in the result set, and that code won’t work in a subquery.

My solution was to create a SQL user defined function.

Before proceeding, I should note that the method I’m posting should only be used if absolutely necessary. In addition to running your main query, you will also be running an additional query for every returned record, which can get pretty expensive.

But if you have a need as I did, this method should do the trick. Te following function accepts an asset GUID and returns the associated keywords in a comma-delimited list:


CREATE FUNCTION getDelimitedKeywords
   (@asset_guid uniqueidentifier )
RETURNS varchar(1000)
AS
BEGIN
   DECLARE @KeywordList varchar(1000)
   SELECT @KeywordList = COALESCE(@KeywordList + ', ', '') + keyword
   FROM keywords
   WHERE asset_guid = @asset_guid

   RETURN ( @KeywordList )
END

Now I can easily include the list in every row of my query by calling the function in my SELECT:


SELECT asset_name, dbo.getDelimitedKeywords(asset_guid) as Keywords
FROM assets

I didn’t research how to achieve the same results in mySQL, but it’s probably pretty easy to do using the GROUP_CONCAT function.

-rG

3 thoughts on “How to Create Delimited Lists of Related Data in Your MS SQL Queries

  1. Without a visual example to see if I understand this right, I’m not sure if I have the same solution or not…but for Oracle. I found after much searching some code that would allow me to pull many related rows from a table and comma separate them into a single column connected to the parent table. i.e. table A has a key to join to table B. table B is many to one relation of A. If my goal is to query A and get a single row back for each record in A but there are say 4 children records in B, normally you would get 4 rows back in your join. This will instead return 1 row, with A’s value in one column and B1, B2, B3, B4 combined in 1 row/column.

    Anyway, I can dig out the code if anyone is interested. It requires creating a custom function and a procedure if I recall correctly. Should be pretty easy to do and its only about 25 lines of code. Email hartwichr@hotmail.com if you are interested in an Oracle solution.

    Ryan

  2. Great info, thank you!
    One issue I ran into was then I was converting a column that was of type int to a varchar. Your example doesn’t allow for that difference. Here is what needs to change in the function. Consider myIDs column as int

    SELECT @myidList = COALESCE(@myidList + ‘, ‘, ”) + CAST(myIDs as varchar(100))

    But… great and useful example. Thanks again!

Comments are closed.