Returning Delimited Lists in SQL

In a past CFUnited SQL presentation, I gave a demonstration on a Transact-SQL (SQL Server) concept known as cross apply which will return a delimited list of items in a query. This is a great way to roll up a one-to-many relationship into a single row. For instance, the following example returns all the reasons for which a sale was one, which may be zero or many. Regardless of the number of reasons, each sales order will only returned once, in one row:

SELECT TOP 100 SOH.SalesOrderID, SOH.OrderDate,
   LEFT(SalesReasonList, LEN(SalesReasonList) - 1) AS SalesReasonList
FROM Sales.SalesOrderHeader SOH CROSS APPLY (
   SELECT SR.Name + ', '
   FROM Sales.SalesOrderHeaderSalesReason SOHSR
      INNER JOIN Sales.SalesReason SR ON SOHSR.SalesReasonID = SR.SalesReasonID
   WHERE SOH.SalesOrderID = SOHSR.SalesOrderID
   ORDER BY SR.Name
   FOR XML PATH('')
) AS Cross1(SalesReasonList)
ORDER BY SOH.SalesOrderID DESC


You can download the full presentation on my presentation page.