On the get the job done front, my client had a question today that, while it makes me cringe a little to answer the way I did, it got him moving again.
[Thanks to Mike Levy for pointing me in the right direction]
My client has a small administrative utility that displays Things. Things can be applied to 0 or more categories. Looks kinda’ like this:
He’s using a 3rd party control for the grid itself which provides really easy filtering, but it wouldn’t work with the data structures to which he had been binding. So he wanted to know how he could return the category listing as a delimited string directly from the T-SQL.
Here’s the gist of his schema (nothing fancy):
How we solved it:
SELECT Id, Name, STUFF(
(SELECT cat.Name + ‘; ‘ AS [text()]
FROM Category AS cat
INNER JOIN ItemCategories AS ic ON cat.Id = ic.CategoryId
WHERE (ic.ItemId = Item.Id) FOR XML PATH(”)
), 1, 0, ”
)
FROM Item
Obviously we would like to move the UI junk out of the DB, but he can deal with that when he understands the 3rd party control a little better. Right now, he’s giving his customers some more features.
