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