Combining Rows into 1 Column, and joined with another table

[xMetalDetectorx]

Consider these tables

Product
productID
styleNumber
description

Tag
tagID
tag

These two tables have many-to-many relationship, so I made another table

ProductTag
productID
tagID

Now I want to get all products and corresponding tags, so the output looks like this. I will make a view of it.

View
productID - styleNum - description - tags
1 - '1111' - 'description text' - 'tag1, tag2, tag3'
2 - '2222' - 'description text' - 'tagX, tagY, tagZ'

I found this link: http://sqlserver-notes.blogspot.com/...ring-from.html
But I am not sure how to implement it in my situation.

[BhumikaNT]

I think, u need to write SP with Cursor. Where u can give where condition with COALESCE statement. If u want to use COALESCE function only..

[ck9663]

The blog stores the concatenated string into a variable. You might need to use a sub-query or you can handle this on your front-end. Also, you might want to consider the PIVOT/UNPIVOT command in sql 2005.

Happy Coding!!!


--- CK

[xMetalDetectorx]

I plan to run a full-text search on that View.
Is there a way to add 2 tables to a full-text catalog, and then search on columns on both of them in one query?

Or should I continue trying to construct that view, and just query on one view..

[xMetalDetectorx]

So far I've been able to get one product with all the info I need with this code:

declare @DelimitedString varchar(max),
@productID varchar(max)
 
SET @productID = '1'
 
SELECT 
@DelimitedString = COALESCE(@DelimitedString+', ' , '') + tblTags.tag
FROM tblProductTag
LEFT JOIN tblTags
ON tblProductTag.tagID = tblTags.tagID
WHERE tblProductTag.productID = @productID
 
 
SELECT tblProducts.productID, tblProducts.styleNum, tblProducts.description, tblProducts.inStock, @DelimitedString as tags
FROM tblProducts
WHERE tblProducts.productID = @productID
 

But I need to get this for all products.. can anyone tell me what im doing wrong there?

[BhumikaNT]

You need to use cursor for the same. Which will fatch @ProductID value from your table and these 2 queries of yours will fine with all the Product.

Rgds,

[ck9663]

pivot your tag table and join it to the other two table.

--- CK