Combining Rows into 1 Column, and joined with another table

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.

re: Combining Rows into 1 Column, and joined with another table

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..

re: Combining Rows into 1 Column, and joined with another table

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

re: Combining Rows into 1 Column, and joined with another table

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..

re: Combining Rows into 1 Column, and joined with another table

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

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

re: Combining Rows into 1 Column, and joined with another table

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,

re: Combining Rows into 1 Column, and joined with another table

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

--- CK