How do I create a recursive query to return a flattened, concatenated string column

sql-server-2008

Solution

with tree as (
   select id, 
          cast(name as varchar(max)) as hierarchy,
          name, 
          description
   from the_table
   where parentID is null
   union all
   select c.id, 
          p.hierarchy + ', ' + c.name,
          c.name,
          c.description
    from the_table c
       join tree p on p.id = c.parentID
) 
select * 
from tree;

Problem

I am using SQL Server 2008 and have a SQL challenge I have never run into before. Consider the following table that represents a hierarchical category list: ``` ID Name Description ParentID --- --------------- ------------------------------- -------- 1 Bicycle Bicycles and Tricycles {null} 2 Wheels Wheels 1 3 Spoked Spoked Wheels 2 4 Skate Boards Skate Boards and accessories {null} 5 Wheels Skate Board Wheels 4 6 Polyurethane Polyurethane Wheels 5 ``` Results I am looking for: ``` ID Heirarchy Description --- --------------------------------------- ------------------------------------ 1 Bicycle Bicycles and Tricycles 2 Bicycle, Wheels Wheels 3 Bicycle, Wheels, Spoked Spoked Wheels 4 Skate Boards Skate Boards and accessories 5 Skate Boards, Wheels Skate Board Wheels 6 Skate Boards, Wheels, Polyurethane Polyurethane Wheels ``` I would like to Query this table and return a name for each row that would represent the hierarchy by concatenating the name(s) of each parent to the child. The hierarchy does not have a pre-set nesting depth and I would like to be able to run this in a single query. How can this be accomplished?

Original source