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

8 replies
  1. Mohammad
    Mohammad says:

    One of the best concatenating articles in the net! Thanks!

    Instead of STUFF function in Using Cross Apply method we can use this alternate also:

    RIGHT(list, LEN(list)-1);

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.