Include parent node ID in hierarchical recordset of child rows I have a table of categories which are linked together by a parent_category_id column. In this query I am able to identify all of the child categories (could be several levels of children) for a specified parent, but I would like to also include the parent id on each row.

WITH level_1 (category_id, category_name) AS (
  SELECT
   category_id, 
   category_name
  FROM category
  WHERE 
   parent_category_id = 1
  UNION ALL
  SELECT 
    c.category_id,
    c.category_name
  FROM category AS c CROSS JOIN level_1 
  WHERE 
    c.parent_category_id = level_1.category_id
)
select * from level_1


The query as written returns the following:

category_id | category_name
         64 | Cows
         6  | Pigs
         11 | Holstein
         23 | Bantu


What I want though is below because I want to group on those parent categories but I won't necessarily know what they are except that they have a parent_category_id = 1.

 root_id | category_id |  category_name
      64 |          64 |  Cows
       6 |           6 |  Pigs
      64 |          11 |  Holstein
       6 |          23 |  Bantu


Category table is like below.

   category_id         |  unique row identifier
   category_name       |  varchar
   category_parent_id  |  foreign key to category table
It's hard to understand your requirements without seeing the definition of category - Presumably, there is a root_id column in that table?

If there is, perhaps you want this?

;WITH level_1 (category_id, category_name, root_id) AS (
    SELECT category_id, 
        category_name,
        root_id
    FROM dbo.category
    WHERE parent_category_id = 1
    UNION ALL
    SELECT c.category_id,
        category_name,
        root_id
    FROM dbo.category AS c 
    CROSS JOIN level_1 
    WHERE c.parent_category_id = level_1.category_id
)
SELECT category_id
    , category_name
    , root_id 
FROM level_1;


Just FYI, it's good practice to begin a CTE with a semicolon, to specify the schema, and avoid using SELECT *