Saturday, March 22, 2008

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 comments:

pez said...

Cheers, this post saved me hours.

Alex said...

Great post! This saved me lots of time!

Mohammad said...

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);

Naomi said...

How will you concatenate rows in SQL Server CE? FOR XML PATH solution is not supported, nor functions are supported...

Plamen Ratchev said...

Hi Naomi,

You can try the first solution from here (using CASE expressions): http://www.projectdmx.com/tsql/rowconcatenate.aspx

However, the goal in SQL Server CE is to use the engine only as storage and simple querying. All other formatting should be done by the client API. I would do the concatenation in the client application, it will be much more easier.

Doug Purnell said...

Thank you Plamen for this post. I have not used Cross Apply or FOR XML PATH before and your post was able to solve a business problem for me, thanks again

Doug

Harish said...

Saved me - I do not know how many hours

Anonymous said...

Thank you very much, your post really helped me.