Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  concatenation and coalesce second 200

Author  Topic  zman574 Starting Member 1 Post Posted - 2011-06-29 : 17:22:54 I have a query that concatenates my website categories together because I have products with 0-885 different categories..... I have come up with this query to put together the first 200 categories but if I exceed the 200 I will not be able to load it to the site, so what I would like to do is set up a query which will give me to top 200 as 1 partmasterid and the next 200 201-400 will give me the same partmasterid but with a different concatination of different categories..... i then will rename the product based on run of function top 200 =xyz1 and run of function 201-400 =xyz2 so the end result I will have the same part show up in this case 2 times each with 200 categories in it..... also if I have a part that is say only in a category 25 times I do not want to return any results so in that case it would not return xyz2 as a part number becasue that would just create products in my store that would not have any categories. Hopefully this make sense: Here is the query create function dbo.temptable1(@PartMasterID int) returns varchar(MAX) as begin declare @output varchar(MAX) select Top 200 @output = coalesce(@output + ', ', '') + categories from dbo.fitmentcats where PartMasterID = @PartMasterID and categories > '' return @output end GO select PartMasterID, dbo.temptable1(PartMasterID) categories from dbo.fitmentcats group by PartMasterID Drop Function dbo.temptable1 Any help would be greatly appreciated Jim JNZ Tech www.jnztech.com