I have 2 tablesPRODUCTS
PROD_ID PROD_NAME PROD_CAT 1 prod1 1 2 prod2 1,3 3 prod3 2,3 CAT_ID CAT_NAME 1 mycat 2 newcat 3 thiscat 1 | prod1 | mycat 2 | prod2 | mycat,thiscat 3 | prod3 | newcat,thiscat
I would suggest you to create another table which will contain the Product_ID and the Category_ID, the way you are storing it now is considered very bad in relational terms and if you can't change it, then use a split function to split the comma separated categoryID and then join it with the category table and then again concatenate the category_Name using FOR XML PATH(''). Use will find a lot of examples of doing this on forums.
Thanks and regards, Rishabh K
thanks for the quick reply!
I think i will have to go with the first solution while it is early in the project!
I would have bigger problems using the xml data