Pushing multiple results into one column

 

It is often the case with relational databases that you have multiple many-to-one relationships between tables in your database.  For example, you might have a table that has order information (order id, customer id, payment details) and another table that simply lists the items purchased during a particular order (order id, product id).

We can do a simple join and get our results back, but if the customer purchased more than one item (e.g. a belt sander, a circular saw, and some #2 pencils) then you will get multiple rows back, like this:

Order ID: Customer ID: Products: Subtotal: Tax:
6549879 1256 Belt Sander $156.78 $25
6549879 1256 Circular Saw $156.78 $25
6549879 1256 #2 Pencils $156.78 $25

It would be better to get your results back in one row, like this:

Order ID: Customer ID: Products: Subtotal: Tax:
6549879 1256 Belt Sander, Circular Saw, #2 Pencils $156.78 $25

The query that I use to get the results into one row is:

SELECT DISTINCT
      [o].[OrderId] AS [Order ID:],
      [o].[CustomerId] AS [Customer ID:],
      (STUFF((SELECT (', ' + [p].[ProductName])
            FROM [dbo].[Products] AS [p]
            INNER JOIN [dbo].[SoldItems] AS [i]
            ON [p].[ProductId] = [i].[ProductId]
            WHERE [i].[OrderId] = [o].[OrderId]
            FOR XML PATH('')), 1, 2, '')) AS [Products:],
      [o].[Subtotal] AS [Subtotal:],
      [o].[Tax] AS [Tax:]
FROM
      [dbo].[Orders] AS [o]
WHERE
[o].[CustomerId] = 1256

 

As far as I know (as far as .net 4.0) there is no equivalent of “STUFF” or “FOR XML PATH” in LINQ.  If you know of an equivalent, I’d love to hear from you.

The other thing you need to know is that this will not work in SQL Server 2000.

Please share any comments you have!

About these ads

4 thoughts on “Pushing multiple results into one column

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