SQL function to consolidate detail rows into a string

Or, how to convert a jagged result set into a CSV from within MS SQL server (2005+):

CREATE FUNCTION
        dbo.GetKeywordList(@VideoModuleID uniqueidentifier)
        RETURNS VARCHAR(Max)
AS
BEGIN
        DECLARE @csv VARCHAR(MAX)

        SELECT @csv = COALESCE(@csv + ', ', '') + Term
        FROM   keyword k
               INNER JOIN VideoModuleKeyword vmk
               ON k.id = vmk.keywordid
        W HERE vmk.videomoduleid=@VideoModuleID

	RETURN @csv
END

NB. Ignore the space in the W word. WordPress doesn’t seem to like me saving the whole word… Here is a stackoverflow article on with the same solution.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s