sql server - Sql Query to find reporting user details -

i have table follow fields

---

userid username reportinguserid

---

 1   vinoth          null  2   kumar            1  3   raja             1  4   ragu             2  5   vignesh          2  6   yoga             3  7   yogesh           2  8   eswar            4  9   esakki           3  .   ...              . 

. .... .

if gave userid 1 query display users reporting him displayed

if gave userid 1

then result be

---

userid username reportinguserid

---

 2   kumar            1  3   raja             1  4   ragu             2  5   vignesh          2  6   yoga             3  7   yogesh           2  8   eswar            4  9   esakki           3  .   ...              . 

. .... .

have @ using recursive cte.

a common table expression (cte) can thought of temporary result set defined within execution scope of single select, insert, update, delete, or create view statement. cte similar derived table in not stored object , lasts duration of query. unlike derived table, cte can self-referencing , can referenced multiple times in same query.

something like

declare @table table( userid int,  username varchar(20), reportinguserid int )   insert @table values (1,'vinoth',null), (2,'kumar',1), (3,'raja',1), (4,'ragu',2), (5,'vignesh',2), (6,'yoga',3), (7,'yogesh',2), (8,'eswar',4), (9,'esakki',3)  declare @userid int = 1  ;with reportingusers (         select  *            @table           reportinguserid = @userid         union         select  t.*            @table t inner join                 reportingusers ru   on  ru.userid = t.reportinguserid ) select  *    reportingusers