manipulating data before grouping & displaying

I got data with recursive data. Additionally every entry is asssigned to a specific role. But not every related child & parent doesn't have to have the same role. Usually, every child should be displayed underneath its parent, but when I group on the role, the entries get split up.Like:

Role x:
Parent 1
Child 1.1
Child 1.2
Parent 2
Role y:
Parent 3
Child 2.1
Child 2.2
Parent 4

I think you get the picture. But It should look like:

Role x:
Parent 1
Child 1.1
Child 1.2
Parent 2
Child 2.1
Child 2.2
Role y:
Parent 3
Parent 4

The easiest way I thought of, was, to manipulate the data of the child entries in the role column, so it matches the same role as their parent. Anyone got any idea of how to accomplish such a thing?

write a recursive sql cursor. Create a temp table with the a new column. For each new level add a space to the new column. So the child would get a " " and the grandchildren would get a bigger space " " and so fourth. Make sure the cursor puts the elements in the correct order as in your example above. The cursor will transfer your dataset and will load the values in the temp table. Then when you print your tree you can do something like.

newcolumn.value & node.value