I had a need to concatenate  and comma separate some multi-row data into an array of values with each having an unknown number of elements, in other words, take a many to one parent-child relationship and collapse the many child rows into the one parent record and separate the child record values with a comma.  In the past, my default method to solve this problem was to build a temporary  table  and then use a loop to iterate through a data set and append the elements by updating rows in the temporary table, or use a common table expression with anchor and recursive members.  Recently I stumbled upon the “stuff” and “for xml” functions.  I had seen these functions  before but never took the time to understand their potential use.  These function can be used to solve the problem mentioned.

T-SQL For XML (Path Mode)

Function Description: A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in sub queries. The top-level FOR XML clause can be used only in the SELECT statement. In sub queries, FOR XML can be used in the INSERT, UPDATE, and DELETE statements. It can also be used in assignment statements.

In a FOR XML clause, you specify one of these modes: RAW, AUTO, EXPLICIT, PATH.  We will only use PATH for this exercise.

The PATH mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner.

The EXPLICIT mode allows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML. It requires a specific format for the resulting rowset that is generated because of query execution. This rowset format is then mapped into XML shape. The power of EXPLICIT mode is to mix attributes and elements at will, create wrappers and nested complex properties, create space-separated values (for example, OrderID attribute may have a list of order ID values), and mixed contents.

I will not list the syntax for this function because it can get pretty complex very quickly for all of the options.  Instead, you can see in the example I just use it to concatenate the rows into a comma separated array.   Any other XML is basically ignored by passing  in the argument “Path (”)”.

(More information on For XML)

T-SQL Stuff Function

Function Description: The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Function Syntax: STUFF ( character_expression , start , length ,character_expression )

(More Information on the Stuff function)

Example Problem

List database users and include all roles of which a user is a member, separated by comma.

Solution using Stuff and For XML

• “For XML” is used to collapse and concatenate the row data into a single array.
• “Stuff” is used to remove the leading comma.
• Note that we are using a correlated subquery when we reference “dp.principal_id” in order to limit our roles and role members to our database principals in the main outer query.

Output: