By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,529 Members | 864 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,529 IT Pros & Developers. It's quick & easy.

Combining Rows into 1 Column, and joined with another table

P: 44
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.
Jul 9 '09 #1
Share this Question
Share on Google+
6 Replies


P: 5
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..
Jul 9 '09 #2

ck9663
Expert 2.5K+
P: 2,878
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
Jul 9 '09 #3

P: 44
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..
Jul 12 '09 #4

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

Expand|Select|Wrap|Line Numbers
  1. declare @DelimitedString varchar(max),
  2. @productID varchar(max)
  3.  
  4. SET @productID = '1'
  5.  
  6. SELECT 
  7. @DelimitedString = COALESCE(@DelimitedString+', ' , '') + tblTags.tag
  8. FROM tblProductTag
  9. LEFT JOIN tblTags
  10. ON tblProductTag.tagID = tblTags.tagID
  11. WHERE tblProductTag.productID = @productID
  12.  
  13.  
  14. SELECT tblProducts.productID, tblProducts.styleNum, tblProducts.description, tblProducts.inStock, @DelimitedString as tags
  15. FROM tblProducts
  16. WHERE tblProducts.productID = @productID
  17.  
But I need to get this for all products.. can anyone tell me what im doing wrong there?
Jul 16 '09 #5

P: 5
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,
Jul 16 '09 #6

ck9663
Expert 2.5K+
P: 2,878
pivot your tag table and join it to the other two table.

--- CK
Jul 16 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.