Small Thing Learned Today

Let's say I have some data regarding the buildings and people who built them - one building obviously can have multiple builders.

BuildingID  BuilderName
----------- ----------------
1           Paul
2           John
3           Bob
1           George
2           Sam
3           Fred
1           Joe
2           Phil

What I need here, is a report in the following format:

BuildingID  builder_list
----------- --------------------------
1           George, Joe, Paul
2           John, Phil, Sam
3           Bob, Fred

I found a very good guide on the problem:

Concatenating row values in Transact-SQL

This is the solution I chose from the suggested ones:

WITH CTE (BuildingID, builder_list, builder_name, length)
 AS(SELECT BuildingID, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
    FROM tblBuildings
    GROUP BY BuildingID
       UNION ALL
    SELECT p.BuildingID, CAST(builder_list +
    CASE WHEN length = 0 THEN '' ELSE ', ' END + BuilderName AS VARCHAR(8000) ), 
    CAST(BuilderName AS VARCHAR(8000)), length + 1
    FROM CTE c
    INNER JOIN tblBuilders p
    ON c.BuildingID = p.BuildingID
    WHERE p.BuilderName > c.builder_name)
  
SELECT BuildingID, builder_list
FROM (SELECT BuildingID, builder_list,
 RANK() OVER (PARTITION BY BuildingID ORDER BY length DESC)
FROM CTE) D (BuildingID, builder_list, rank)
WHERE rank = 1;

It looks a bit complex to understand, but does exactly what I need!

by Evgeny. Also posted on my website