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.