Row Concatenation with FOR XML PATH

Many times it is needed for reporting purposes to summarize normalized data into groups or lists of values. This is also known as rows concatenation. Some reporting and client side tools support this directly. Here is one approach to solve this in T-SQL using FOR XML PATH.

CREATE TABLE Products (
 sku INT PRIMARY KEY,
 product_desc VARCHAR(35));
 
CREATE TABLE Departments (
 department_nbr INT PRIMARY KEY,
 department_title VARCHAR(35));
 
CREATE TABLE DepartmentProducts (
 department_nbr INT 
   REFERENCES Departments (department_nbr),
 sku INT 
   REFERENCES Products (sku),
 PRIMARY KEY (department_nbr, sku));
 
INSERT INTO Products VALUES (1, 'Book');
INSERT INTO Products VALUES (2, 'Magazine');
INSERT INTO Products VALUES (3, 'DVD');
INSERT INTO Products VALUES (4, 'Video');
INSERT INTO Products VALUES (5, 'CD');
INSERT INTO Products VALUES (6, 'Map');
 
INSERT INTO Departments VALUES (1, 'Reference');
INSERT INTO Departments VALUES (2, 'Periodicals');
INSERT INTO Departments VALUES (3, 'Entertainment');
 
INSERT INTO DepartmentProducts VALUES (1, 1);
INSERT INTO DepartmentProducts VALUES (1, 6);
INSERT INTO DepartmentProducts VALUES (2, 2);
INSERT INTO DepartmentProducts VALUES (3, 3);
INSERT INTO DepartmentProducts VALUES (3, 4);
INSERT INTO DepartmentProducts VALUES (3, 5);
 
-- Using correlated subquery 
SELECT D.department_nbr,
       D.department_title,
       STUFF((SELECT ',' + product_desc
              FROM DepartmentProducts AS DP
              JOIN Products AS P
                ON P.sku = DP.sku
              WHERE DP.department_nbr = D.department_nbr
              ORDER BY product_desc
              FOR XML PATH('')), 1, 1, '') AS product_list
FROM Departments AS D;
 
-- Using CROSS APPLY
SELECT D.department_nbr,
       D.department_title,
       STUFF(P.product_list, 1, 1, '') AS product_list
FROM Departments AS D
CROSS APPLY (SELECT ',' + product_desc
             FROM DepartmentProducts AS DP
             JOIN Products AS P
               ON P.sku = DP.sku
             WHERE DP.department_nbr = D.department_nbr
             ORDER BY product_desc
             FOR XML PATH('')) AS P (product_list);
 
-- Results             
department_nbr department_title product_list
-------------- ---------------- ------------
1              Reference        Book,Map
2              Periodicals      Magazine
3              Entertainment    CD,DVD,Video

While this method is often called the XML blackbox method, the explanation of this effect using FOR XML PATH is simple. Normally the PATH clause is used with input string that indicates the name of the wrapper element that will be created. However, using the PATH clause with empty string as input results in skipping the wrapper element generation. And since the content is retrieved as text it achieves the effect of concatenation.

Resources:

What’s New in FOR XML in Microsoft SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms345137.aspx