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