Category Path Query

[HomeIndoor]

I am looking for a simply query that will generate the category path as it should be uploaded via excel or wsi

10 minute job for all you SQL guru's out there.

I want to generate all of the paths for all of the categories that we currently offer.
Thanks in advance.

I can pay for your services.

[esedirect]

You probably want something like this:

WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS
(
SELECT c.ParentCategoryID, c.CategoryID, c.Name, 0 AS ComputedLevel, CAST('\'+c.Name AS NVARCHAR(255))
FROM Category AS c
WHERE ParentCategoryID = 0
AND Deleted=0
AND Published=1
UNION ALL
SELECT c.ParentCategoryID, c.CategoryID, c.Name, ComputedLevel + 1, CAST(s.Sort + '\'+c.Name AS NVARCHAR(255))
FROM Category AS c
INNER JOIN Categories AS s ON c.ParentCategoryID = s.CategoryID
WHERE Deleted=0
AND Published=1
)

SELECT ParentCategoryID, CategoryID, Name, ComputedLevel, Sort
FROM Categories
ORDER BY 5

[HomeIndoor]

WOW, I woke up this morning exhausted from having to rebuild our database and server, only sleeping 3 hours every night since our server crashed. I made the wife some breakfast, got me some coffee and opened my email and first thing I see is your response. I looked over your query and made this oh, this is well written and then ran your query and all I can say is wow, you are the man .

I have been trying to combat this problem with the category paths for several days now, since I only have the category id and category name which is over 500 categories. I was hoping I could use the category id when updating via wsi but turns out I could not, but you have made my day.

Forget the coffee, You just woke me up.

Thank you very much. I am glad that their are still good people out there.
If you need anything, let me know.

[xjeddan143]

Thank you for the code. I really need this query for my System that I developing now.

Thank you

[Cano]

wasn't looking for this, but it will come in handy later

Thanks

[harsha.gus]

You probably want something like this:

WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS
(
SELECT c.ParentCategoryID, c.CategoryID, c.Name, 0 AS ComputedLevel, CAST('\'+c.Name AS NVARCHAR(255))
FROM Category AS c
WHERE ParentCategoryID = 0
AND Deleted=0
AND Published=1
UNION ALL
SELECT c.ParentCategoryID, c.CategoryID, c.Name, ComputedLevel + 1, CAST(s.Sort + '\'+c.Name AS NVARCHAR(255))
FROM Category AS c
INNER JOIN Categories AS s ON c.ParentCategoryID = s.CategoryID
WHERE Deleted=0
AND Published=1
)

SELECT ParentCategoryID, CategoryID, Name, ComputedLevel, Sort
FROM Categories
ORDER BY 5

is it possible to get all records as in this query with a condition where ParentCategoryID='3' (it should go through a loop)
let say if i have two category under categoryID 3 and those two categories have 4 subcategories under it (each). then the query should bring up total 10 (1+4+4) records not two

thanks

[BFG 9000]

Just change :-

WHERE ParentCategoryID = 0

to

WHERE ParentCategoryID = 3



TTFN

BFG

[harsha.gus]

Just change :-

WHERE ParentCategoryID = 0

to

WHERE ParentCategoryID = 3



TTFN


BFG

i was dumb, i didn't even pay attention to the code.
i am trying to join Product and ProductVariant Tables to the above query and retries all the products under a category.
can you please help me joining Product and ProductVariant Tables to the above query.

I really appreciate your help.

thanks

[BFG 9000]

What's the exact output you want?

Is it just a list of products that are in CategoryID 4 or any of its subcategories?


TTFN

BFG

[harsha.gus]

What's the exact output you want?

Is it just a list of products that are in CategoryID 4 or any of its subcategories?


TTFN

BFG

I want all the product list under a main category and its subcategories with the Category Map, I have used your code ans made a temp table and got what i am looking for. But i am not sure if that is an efficient way. I am attaching that sql file.
Hope you can suggest me any efficient way to achieve it.
please use this link to download the text file containing the query
http://www.sendspace.com/file/5d6gm5


Thanks