Group concatenation in T-SQL using CTE

In Oracle you have <a title="listagg function" href="http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php" target="_blank">listagg()</a> and in MySQL you have <a title="group_concat() function" href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat" target="_blank">group_concat()</a> to create string aggregations. But what do you have in T-SQL? The simple answer is nothing, yet, and we’re all hoping for a change in the future. But until then it’s up to us to do it by hand.

In a previous post I showed how to nest up hierarchical trees with parent-child relationships. In this post I’m going to continue on that one and create a list of the managers each person has.

The previous post used a recursive CTE to create this result:

baseUserId  userId      userName         managerId   steps
----------- ----------- ---------------- ----------- -----------
1           1           John             NULL        0
2           2           Charles          1           0
2           1           John             NULL        1
3           3           Nicolas          2           0
3           2           Charles          1           1
3           1           John             NULL        2
4           4           Neil             5           0
4           5           Lynn             1           1
4           1           John             NULL        2
5           5           Lynn             1           0
5           1           John             NULL        1
6           6           Vince            5           0
6           5           Lynn             1           1
6           1           John             NULL        2
7           7           Claire           6           0
7           6           Vince            5           1
7           5           Lynn             1           2
7           1           John             NULL        3

(18 row(s) affected)

This result can be useful for further processing but it might not be that visually clear right away. To aggregate the manager names and show them in a one-line-list next to the user would be much more intuitive. It’s actually not that difficult and with just a little change in the query we can get an answer like this instead:

userId      userName       managerNames         numManagers
----------- -------------- -------------------- -----------
1           John                                0
7           Claire         Vince Lynn John      3
6           Vince          Lynn John            2
5           Lynn           John                 1
4           Neil           Lynn John            2
3           Nicolas        Charles John         2
2           Charles        John                 1

(7 row(s) affected)

What we need to do is concatenate the manager’s names in the recursive loop (results in 18 rows with all combinations in the hierarchy) and then filter out the lines where we’ve reached the top manager and thus completed the list of managerNames. This last part is done by finally filtering by setting WHERE managerId IS WHERE (on line 28 in the query below).

One other tricky thing with aggregating the manager’s names, is that the length of the string in the anchor section and the recursive section has to be the same. If the anchor has an nvarchar(50) then the total length of the new concatenation in the recursive part has to be 50 as well. Some serious casting is needed here to make it work but it’s not that complex.

WITH UserCTE AS (
  SELECT 
    userId as baseUserId, 
    userName as baseUserName, 
    userId, 
    userName, 
    managerId, 
    CAST('' AS NVARCHAR(50)) as managerNames, 
    0 AS numManagers
  FROM dbo.Users
    
  UNION ALL
  
  SELECT 
    usr.baseUserId,
    usr.baseUserName, 
    mgr.userId, 
    mgr.userName, 
    mgr.managerId, 
    LTRIM(CAST(usr.managerNames AS NVARCHAR(14)) + N' ' + 
         CAST(mgr.userName AS NVARCHAR(35))) AS managerNames, 
    usr.numManagers +1 AS numManagers
  FROM UserCTE AS usr
    INNER JOIN dbo.Users AS mgr
      ON usr.managerId = mgr.userId
)
SELECT baseUserId AS userId, baseUserName AS userName, managerNames, numManagers 
  FROM UserCTE AS u 
  WHERE managerId IS NULL;

Note line 20 in the code where the length of the strings (14 + 1 + 35) equals the same length as the string in the anchor (50) on line 8.

One final thing we need to change from the original code is that we need to bring the user’s name from the anchor section because that’s the user we’re tracking through the hierarchy. That is being done by the baseUserName that is just passed on in the recursive section.

More reading

• Article by Anith Sen on different string aggregation techniques in T-SQL.
• List of aggregate function that are available in T-SQL 2012
• Post by Rob Farley on why Coalesce is not the answer to string concatenation in T-SQL