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 ;-)