Need some help with a SQL query (SQL Server 2005) to transform this data

Question

Here's a simplistic look at the table I have

Category

CategoryId
CategoryName
ParentCategoryId

As you can see, the catalog table is a self referencing table, so recursing it is possible by starting at 0, which is the fake "root" category (could just as well be null, but it's not).

Sample Data:

1, Apples, 0
5, Yummy, 1
10, Really Yummy, 5
15, Yucky, 0
18, Some Sub Cat, 15
20, Some Deep Sub Cat, 18
25, Some Deep Sub Cat 2, 18

The deepest any category hierarchy can be is 4 deep, and I'm trying to get an output that looks like this:

CatId, [up to 4 deep of category names on the hierarchy in separate columns, or null if none]
1, Apples, NULL, NULL, NULL
5, Apples, Yummy, NULL, NULL
10, Apples, Yummy, Really Yummy, NULL
15, Apples, Yucky, NULL, NULL
18, Apples, Yucky, Some Sub Cat, NULL
20, Apples, Yucky, Some Sub Cat, Some Deep Sub Cat
25, Apples, Yucky, Some Sub Cat, Some Deep Sub Cat 2

This SQL is close, but it generates it backwards, left justified

select c1.categoryid, c1.name, c2.name, c3.name, c4.name
from category c1
    left outer join category c2
        on c1.parentcategoryid = c2.categoryid
    left outer join category c3
        on c2.parentcategoryid = c3.categoryid
    left outer join category c4
        on c3.parentcategoryid = c4.categoryid

Any SQL geniuses have some good ideas?

Carl Manaster · Accepted Answer · 2009-07-21 22:26:33Z

1

select c1.CategoryId as id,
       c1.CategoryName as n1,
       c2.CategoryName as n2,
       c3.CategoryName as n3,
       c4.CategoryName as n4
from            Category c1
left outer join Category c2 on c2.ParentCategoryId = c1.CategoryId
left outer join Category c3 on c3.ParentCategoryId = c2.CategoryId
left outer join Category c4 on c4.ParentCategoryId = c3.CategoryId
where c1.parentcategoryid = 0;

(per your edit: you've just got ParentCategoryId and CategoryID flipped in the joins.)

edited Jul 21, 2009 at 22:26

answered Jul 21, 2009 at 22:07

Carl Manaster

40.5k17 gold badges109 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TheSoftwareJedi Over a year ago

Quite close... A couple changes. Needs a where clause "where c1.parentcategoryid = 0" or it prints bum results. Then it needs a case statement maybe to determine the id to show (deepest level)? Thoughts?

Carl Manaster Over a year ago

Thanks; I've added the WHERE clause you suggest. I would avoid the CASE statement - I don't think SQL handles conditional flow logic of that sort very elegantly - and simply write the other three queries as desired. I know some developers hate duplication enough to go to the trouble of writing and maintaining the case statement, but for me it's a better fit with the language.

TheSoftwareJedi Over a year ago

then this is wrong, the categoryid printed is the root categoryid. I needed to print the deepest level category. The case statement worked fine.

TheSoftwareJedi Over a year ago

This leaves out non-leaf categories

Collectives™ on Stack Overflow

Need some help with a SQL query (SQL Server 2005) to transform this data

Category

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Category

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related