I have a table references to itself. User table: id, username, managerid and managerid links back to id Now, I want to get all the managers including direct manager, manager of direct manager, so on and so forth... The problem is that I do not want to have a unstop recursive sql. So, I want to check if an id alreay in a list, I will not include it anymore. Here is my sql for that:

with 
    --the relation for all the subparts of ozsolar including itself
    all_managers (id, username, managerid, idlist) as
    (
        --seed that is the ozsola part
        select u1.id, u1.username, u1.managerid, ' '
        from users u1, users u2
        where u1.id = u2.managerid
        and u2.id = 6

        UNION ALL

        select u.id, u.username, u.managerid, idlist + ' ' + u.id
        from all_managers a, users u
        where a.managerid = u.id
        and charindex(cast(u.id as nvarchar(5)), idlist) != 0
    )   
--select the total number of subparts and group by subpart
select id, username
from all_managers;

The problem is that in this line: select u1.id, u1.username, u1.managerid, ' ' The sqlserver complains with me that I can not put ' ' as the initialized for idlist. nvarchar(40) does not work as well. I do not know how to declare it inside a common table expression like this one. Usually, in db2, I can just put varchar(40)