How To Use Common Table Expression And Check No Duplication In Sql Server

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 dir

Solution 1:

Solving the casting issue is easy enough. You simply need to cast both values in the union to the same type:

With
    all_managers (id, username, managerid, idlist) as
    (
        Sselect u1.id, u1.username, u1.managerid
            , Cast(' 'Asvarchar(40))
        From users As u1
            CrossJoin users As u2
        Where u1.id = u2.managerid
            and u2.id =6UnionAllSelect u.id, u.username, u.managerid
            , Cast(Coalesce(idlist +' ','') +Cast(u.id Asvarchar(10)) Asvarchar(40))
        From all_managers As a
            CrossJoin users As u
        Where a.managerid = u.id
            and CharIndex(Cast(u.id as nvarchar(5)), idlist) !=0
    )   
Select id, username, idlist
From all_managers;

However, I'm still not quite sure what you are trying to achieve. Can you show us some sample data of what should and should not be returned in the resultset?