Not sure why, but I seem to be on a T-SQL kick lately - so here's another T-SQL post. One of my favorite T-SQL hacks ever is one that can flatten out data by taking a value from multiple rows and concatenating the values into a single string.

Let's say, for example, that you have a “training class” table with a 1:M child table containing dates the class will be held and you want to just have a single string that lists the dates for each class. Or maybe that you have a “project” table with a 1:M child, each row containing a person assigned to that project and you want to have a list of all the members of the project like this: “Ryan Farley, Bobo Brown, Joe Blow”. Apparently this trick has been around a while. I first learned of it from Scot Becker a year or so ago and I've loved it ever since.

Take a look at this example of parent-child tables and their data:

Now, if I wanted to get a single string of child records for a given parent I could do this:

declare @list varchar(1000)
set @list = ''

select @list = @list + fieldvalue + ' ' from childtable where parentid = @id

return @list

This would result in the following value returned for the ParentID of 1:

Value 1-1 Value 1-2 Value 1-3

This can have some cool uses. Let's say you want to kick out something to a customer outlining the products in their order. Just the names delimited by commas. You could use this (using the Northwind database)

declare @prodlist varchar(1000) 
set @prodlist = ''

select 
    @prodlist = @prodlist + p.productname + ', ' 

from
    orders o 

    inner join [order details] o2 
    on o.orderid = o2.orderid 

    inner join products p 
    on o2.productid = p.productid 

where 
    o.orderid = @id

--trim off extra comma at end
if @prodlist <> '' set @prodlist = left(@prodlist, len(@prodlist) - 2)

return @prodlist

Now you'll have a comma delimited string of products for the order to use as you need. No cursors/loops used. I've used this in many different ways, from listing a string of dates for an event, or even building a Page1/Page2/Page3.aspx kind of URL for hierarchical pages in a CMS/portal system. Quite a cool trick IMO.

A few things to keep in mind. This is in no way an optimal route if you have a lot of rows of data to work with. This will be slow with medium to large sets of data. You can only use a single field in this since it is an assigning select statement and cannot be mixed with data-retrieval, so the usual route is to stick it in a function where you pass in a ID and get back the string. Add the call to the function in your data-retrieving select statement and things will get slow if you have the potential for a large amount of rows. But with that said, Scot's post outlines a few crude speed tests and states the following:

To do an unscientific performance test, I created a table loaded with a million numbers and summed them via a) the TSQL sum aggregate function, b) the method above and c) a cursor. On my (old) laptop, the sum function took 1,350 milliseconds, the method above took 1,213 milliseconds, and the cursor took 67,206 milliseconds.

If the trick fits the need, I use it. This one is the cat's meow. I fell in love with this one the moment I read it from Scot a year+ ago. You had me at select @s = @s + person_name ;-)