Monthly Archives: July 2011
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.