Concatenating Data Using CROSS APPLY and FOR XML PATH

While creating reports in SSRS, there have been times that I have needed to concatenate multiple rows of data into one cell. Using the example below, we will create a report that lists each Supplier and a concatenated list of Products, all of which will be contained in one row of the report.

Example of the data:

Example of how the data is returned on the report and the desired state:

Current                                                   Desired State

        

First, we want to create a query that will pull all of the Products into one cell, with respect to each Supplier. This can be accomplished using the FOR XML clause with a mode of PATH. (For an explanation of the FOR XML clause, you can view Microsoft’s webpage.) In addition to FOR XML PATH, we also need to utilize CROSS APPLY, which functions as a sort of loop, evaluating the inner query for each row in the outer query. (For further details on CROSS APPLY, see Microsoft’s webpage)

To concatenate the Products, we will use the query below. We will leave the path empty (i.e. path(”)) to avoid creating an element tag around each product.

Notice, this is just a list of all of the Products in the table. I have added ‘~~’ as a delimiter for each product, we’ll use a REPLACE function later to remove this and add a carriage return and line feed.

Now, we will add in the CROSS APPLY, which will create one row for each distinct supplier. You can see in the example below that the previous query is now a sub-query and I have added a WHERE clause to compare the Supplier on the outer query to the Supplier on the inner query.

For the last step in the creation of the query, we are going to replace the ‘~~’ with a carriage return and line feed so the products will be listed one per line. In the query result below, you’ll notice the ‘~~’ is now gone, but you will not be able to see the products appear on each line due to the Grid view in SSMS. However, we will be able to see the change in the report, which will be our final step in this exercise.

We are almost finished!

After entering the query above in the dataset for the report, the rendered result now shows each Supplier and the list of the respective Products all within one row. We’re done!

 

Thank you for taking the time to visit this blog. Please let me know if you have any questions or have other topics that you’d like to see posted. Have a great day!

 

 

July 17, 2013