March 4, 2009 at 9:13 am
I have data that looks like this:
artID title catid
53 Maximixing Building Performance 18
53 Maximixing Building Performance 22
53 Maximixing Building Performance 25
53 Maximixing Building Performance 69
53 Maximixing Building Performance 101
53 Maximixing Building Performance 110
53 Maximixing Building Performance 117
53 Maximixing Building Performance 207
82 Total Energy Retrofit 24
82 Total Energy Retrofit 27
82 Total Energy Retrofit 100
82 Total Energy Retrofit 101
82 Total Energy Retrofit 102
82 Total Energy Retrofit 111
I want to process this data and have it look like this:
artID title catid
53 Maximixing Building Performance 18, 22, 25, 69, 101, 110, 117, 207
82 Total Energy Retrofit 24, 27, 100, 101, 102, 111
Any tips, hints, or suggestions would be appreciated. Thank you.
March 4, 2009 at 10:07 am
March 4, 2009 at 10:34 am
I do not know your table structure, but you can do something like the following.
--function to concatenate categories into comma seperated list by title
CREATE FUNCTION dbo.fn_GetCategoriesForTitle ( @TitleId int )
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @Categories varchar(max)
SELECT @Categories = COALESCE(@Categories + ', ', '') + R.CatId
FROM ( SELECT DISTINCT
CAST(CatId as varchar) as CatId
FROM dbo.TitleCategories
WHERE TitleId = @TitleId ) R
RETURN @Categories
END
GO
--return titles and comma seperated list of categories
SELECT
T.ArtId,
T.Title,
dbo.fn_GetCategoriesForTitle(T.TitleId) as CatId
FROM dbo.Titles
March 4, 2009 at 12:23 pm
Thanks this function worked. Of course, I had to change it a bit but it is one of those lessons learned for future use. Again, Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply