Denormalizing records into a string.

[travisbrown]

I'm a bit perplexed how to do this. Usually I'd write a recursive function in ASP, but I need this as part of the data results.

I have a categories table.

category_id (pk)
category_name
category_parent

parent_category is so categories can be subjugated to other categories.


1 CAT-A 0
2 CAT-B 0
3 CAT-A1 1
4 CAT-A2 1
5 CAT-A11 3
6 CAT-B1 2

What I need is to return denormalized rows for each category branch like below.

CAT-A/CAT A1/CAT-A11
CAT-A/CAT A2
CAT-B/CAT-B1

Would you start at the lowest node (below) and walk back, or do a forward-looking recursion?

SELECT tbl_category.category_id, tbl_category.category_name, tbl_category.category_parent, tbl_category.category_keyword FROM tbl_category LEFT OUTER JOIN tbl_category tbl_category_1 ON tbl_category.category_id = tbl_category_1.category_parent WHERE (tbl_category_1.category_parent IS NULL)

 

[r937]

i would do a top-down series of LEFT OUTER JOINs -- as many as necessary to span the deepest level

SELECT top.category_name AS top_category_name
     , one.category_name AS one_category_name
     , two.category_name AS two_category_name
     , thr.category_name AS thr_category_name
  FROM tbl_category AS top
LEFT OUTER
  JOIN tbl_category AS one
    ON one.category_parent = top.category_id
LEFT OUTER
  JOIN tbl_category AS two
    ON two.category_parent = one.category_id
LEFT OUTER
  JOIN tbl_category AS thr
    ON thr.category_parent = two.category_id
 WHERE top.category_parent IS NULL
ORDER 
    BY top_category_name 
     , one_category_name 
     , two_category_name 
     , thr_category_name

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[travisbrown]

Hmm...what if I don't know how deep it goes? Sounds like I might need a UDF.

I don't need to split the results into separate columns - just delimit with a forward slash.

Also, I only need to show complete strings - i.e. if CAT-A has child categories CAT-A1 AND CAT-B1, then I'd never return CAT-A - only the full lineages.