Flattening Table Rows Into A Column (Aggregate Concatenation)

I was trying to flatten rows from a table into a single column, with each value separated by a comma – something known as aggregate concatenation.  The way I’ve done stuff like this in the past is something like the following:

SELECT
  m.PersonGuid,
  m.ColleagueGuid,
  MAX(CASE WHEN m.Seq = 1 THEN m.Source ELSE '' END) + 
    MAX(CASE WHEN m.Seq = 2 THEN ',' + m.Source ELSE '' END) + 
    MAX(CASE WHEN m.Seq = 3 THEN ',' + m.Source ELSE '' END)
FROM
  (SELECT
    s1.PersonGuid,
    s1.ColleagueGuid,
    s1.Source,
    COUNT(s2.Source) Seq
  FROM
    (SELECT 
      PersonGuid,
      ColleagueGuid,
      Source
    FROM 
      Contact_Person_Colleague_Source
    ) s1,
    (SELECT 
      PersonGuid,
      ColleagueGuid,
      Source
    FROM 
      Contact_Person_Colleague_Source
    ) s2
  WHERE
    s1.PersonGuid = s2.PersonGuid
  AND
    s2.ColleagueGuid = s2.ColleagueGuid
  AND
    s1.Source >= s2.Source
  GROUP BY
    s1.PersonGuid,
    s1.ColleagueGuid,
    s1.Source
  ) m
GROUP BY
  m.PersonGuid,
  m.ColleagueGuid

However, I’ve only used the above statement when doing a quick, on-the-fly query; I’ve never used it in production code.  Thus I wanted to find some other ways that were more appropriate for production code – ways that were possibly cleaner, easier to read, and more performant. 

One thing I tried was moving the flattening into the following function:

CREATE FUNCTION [dbo].[FlattenColleagueSources] ()
RETURNS NVARCHAR(500)
AS
BEGIN
  DECLARE @sources NVARCHAR(500)
  SET @sources = NULL
  
  SELECT @sources = COALESCE(@sources + ',','') + s.[Source]
  FROM 
    (SELECT [Source] 
    FROM [dbo].[Contact_Person_Colleague_Source]) AS s

  RETURN @sources
END

I saw this and this, but it doesn’t look like they apply because I don’t use ORDER BY (yet), let alone have any expressions or operators in the ORDER BY.

I next tried the following query, which uses the FOR XML clause to do the flattening (and a STUFF function to remove the leading comma):

SELECT
  cpc.[PersonGuid],
  cpc.[ColleagueGuid],
  STUFF(
    (SELECT 
      ',' + cpcs.[Source] 
    FROM 
      [dbo].[Contact_Person_Colleague_Source] cpcs
    WHERE
      cpcs.[PersonGuid] = cpc.[PersonGuid]
    AND
      cpcs.[ColleagueGuid] = cpc.[ColleagueGuid]
    FOR XML PATH('')), 1, 1, ''
  ) AS [Sources]
FROM
  [Contact_Person_Colleague] AS cpc 

Then I found an interesting article that lists a bunch of ways to concatenate table rows.  Whoa.

This may change after I’ve done some performance testing, but for now I’m using the query that utilizes the FOR XML clause.  It’s always fascinating, though, to see the myriad of ways to pull the same information from a database.  And of course, let me know of any other ways to do aggregate concatenation.