adding heirarchal data to SQL Server queries as a comma separated list
A system that I work on uses views to get all of its data. The middle tier is basically engineered to do everything through views, so I have to think of how to do everything using views. Normally this isn't a problem, but I came across a situation where I had to have records that could be members of zero, one, or many categories and I needed to display that data. I thought about it for a while, and this is what I came up with.
First I needed a function that, given an ID of a record, would generate a comma separated list of the categories that the record was a member of. This was the tough part, but here it is:
CREATE FUNCTION CatCategories (@OrganizationID int)
RETURNS varchar(5000) AS
BEGIN
DECLARE @CatList varchar(5000)
SELECT @CatList = COALESCE(@CatList + ',','') +c.CName
FROM Categories c join SubCategories_Xref x on x.CategoryID = c.ID
join InformationR r on r.ID = x.InformationID
WHERE r.ID = @OrganizationID
RETURN @CatList
END
The next step was to call it in a view, which is actually extremely easy:
SELECT ID, Organization, dbo.CatCategories(ID) AS SName
FROM dbo.InformationR r
And that was it. It took me a while to figure out how to do the function without a cursor