Garden Antiques For Sale

SQL: How do you comma-separate values from multiple columns in groups according to another column.?

For example, if you visit http://www.sql-tutorial.com/sql-aggregate-functions-sql-tutorial/ and look at the first table they have displayed, and only using the OrderPrice and CustomerName columns, how would a SELECT statement look in order to produce the following result:
Smith 160,420
Johnson 190
Baldwin 500,2000
Wood 1000

Oracle and MySQL have functions that will allow this, but with MS-SQL 2005 you need to be creative. Monkeying with output from one of the XML functions, you can get results with something like:

SELECT CustomerName,
left(stuff((SELECT ', ' + OrderPrice
FROM Orders Z2
WHERE Z2.CustomerName = Z1.CustomerName
ORDER BY OrderID
FOR XML PATH('')), 1, 1, ''), 56) [Order Prices]
from dbo.Orders Z1
group by CustomerName

Unless your order price is a string, you'll need to do a CAST and perhaps a xTRIM on it to a character type.

There are also several examples of CLR code out on the Internet that will be bit more efficient and certainly easier to use. In short, they make a user aggregate function that mimics the Oracle and MySQL function--sadly all examples I've seen have problems with NULL values and having an embedded function (such as ISNULL or COALESCE) within.

Read the rest of this entry »