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.

Advertisements

Posted on July 26, 2011, in SQL, WebTricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: