Thread: Extract category information

I am trying to generate a product feed that contains information such as productname, sku, category, genre etc.

The category information should be laid out like: Helmets, Armor & Shields\Helmets\"300" Spartan Helmet

The slashes separate the categories and sub categories so the parent category is Helmets, Armor & Shields and subcategory is Helmets.

Similarly genre, manufacturer information needs to be obtained.

Any thoughts on how to recursively obtain all the information for categories, genres, manufacturers etc for every product?

I tried looking for the code that generates the breadcrumbs but didn't have much luck. Is there a stored proc that does this?

Appreciate any help.

Take a look in google for recursive treeviewand tsql. That should get you started.

Appreciate you pointing me out to what I should look for. Here is a link to some useful stuff that i found: http://www.sqlteam.com/article/more-...archies-in-sql

Once I figure out the complete solution I will post it as well

I am using this script..

select parentcategory.name, category.name, product.name, productcategory.productid, sku
from productcategory
left join product on productcategory.productid=product.productid
left join category on category.categoryID=productcategory.categoryID
left join category as parentcategory on category.parentcategoryid=parentcategory.categoryi d
order by product.productid

Similarly you can obtain genres, sections, manufacturers and other "categories"