The problem is that your subquery will return more than one value - or it could - and it is in a position where SQL can only accept a single value per row - not a variable number of values. Effectively, what your query is asking for is SQL to return a tree structure, rather than a flat row based structure.
id Headers
1 Joe
Mike
Dave
2 Joe
Sue
3 Dave
SQL is saying it can't do that.
What are you trying to achive that you think this is a solution?