SQL Server - Get Parent values from Child recursively

Question

So I've been struggling with this for the past hour or so now

I'm writing a script to extract some data in a nice format for me to use in another process but I can't quite figure out this particular bit (I don't use SQL Server much)

I've got several tables that are all involved in this script that link together to get all the information. All of these tables are just using fake data and names but it shows the structure hopefully

tblCategories

cat_id  cat_name
1       Trousers
2       Tracksuits
3       Woolen

tblCategoryHierarchy

ch_parentid  ch_childid
0            1
1            2
2            3

I've also got my product table which has the cat_id in it

tblProduct

p_id  p_name       p_cat_id
1     Red Trouser  3

So from this, I want to display the product id, name and the hierarchy of all categories linked to the one in the tblProduct.

So for this example, it would display:

id  name         cats
1   Red Trouser  Trousers > Tracksuits > Woolen

Hopefully somebody can give me a hand here! Thanks

Answer 1

Try this:

;WITH CTE AS (
   SELECT p.p_id AS id, p.p_cat_id, 1 AS level,
          p.p_name, CAST(c.cat_name AS VARCHAR(200)) AS cat
   FROM tblProduct AS p
   JOIN tblCategories AS c ON p.p_cat_id = c.cat_id

   UNION ALL

   SELECT c.id, ch.ch_parentid AS cat_id, level = c.level + 1,
          c.p_name, CAST(c2.cat_name AS VARCHAR(200)) AS cat
   FROM tblCategoryHierarchy AS ch
   JOIN CTE AS c ON ch.ch_childid = c.p_cat_id
   JOIN tblCategories AS c2 ON ch.ch_parentid = c2.cat_id
)
SELECT id, p_name,
       STUFF(REPLACE((SELECT CONCAT('>', cat) 
                      FROM CTE
                      ORDER BY level DESC
                      FOR XML PATH('')), '>', '>'), 1, 1, '') AS cat
FROM CTE

The recursive part of the query returns all categories from child up to parent level. The query uses FOR XML PATH in order to concatenate category names in reverse level order (i.e. from parent to child).

REPLACE is used because '>' character is rendered as 'gt;' from FOR XML PATH.