SQLServer – Convert multiple rows into one row, comma delimited.
SQLServer – Convert multiple rows into one row, comma delimited.
Working for my current client, one of the reports that needed to be built was an Order record that showed the items listed but as a comma delimited string. Of course, the database tables show each item as a new record which is assigned to master order record. You’ve all seen this before.
Order Table
| OrderID | OrderNumber |
| 1 | X1 |
| 2 | X2 |
| 3 | X3 |
Order Detail Table
| OrderID | OrderDetailID | Item |
| 1 | 1 | O1Item1 |
| 1 | 2 | O1Item2 |
| 2 | 3 | O2Item1 |
| 2 | 4 | O2Item2 |
| 3 | 5 | O3Item1 |
The SSRS report needs to show 3 records with the Items listed separated by commas.
| Order | Items |
| X1 | O1Item1, O1Item2 |
| X2 | O2Item1, O2Item2 |
| X3 | O3Item1 |
I did some digging around on the internet and found a few options
- XML
- Recursive CTE
- Cursor
I’m sure there are a few more out there, but these seemed to be the most reasonable.
In the end, I decided to use the XML because it was much easier to write and performed well.
Here is what I ended up with.
SELECT DISTINCT
OrderDate,
OrderNumber,
Items = SUBSTRING( ( SELECT ‘,’ + ItemCode
FROM tblOrders t2
WHERE t1.OrderNumber = t2.OrderNumber FOR XML PATH(”), elements)
,2,500)
FROM tblOrders t1
Comments