set nocount on
 
  declare @Exemplo table 
  ( 
      ID int  Primary key NOT NULL ,
      PAI int,
      MENU nvarchar(100),
      Salary money
   )
 
  /* Montado tabela teste */
  insert into @Exemplo values(1,null,'PAI',10)
  insert into @Exemplo values(2,1,'FILHO 1',9)
  insert into @Exemplo values(3,1,'FILHO 2',8)
  insert into @Exemplo values(4,2,'NETO 1',7)
  insert into @Exemplo values(5,2,'NETO 2',6)
  insert into @Exemplo values(6,1,'FILHO 2',5)
  insert into @Exemplo values(7,4,'BISNETO 1',4)
  insert into @Exemplo values(8,4,'BISNETO 2',3)
  insert into @Exemplo values(9,6,'BISNETO 3',2)
  insert into @Exemplo values(10,8,'TATARANETO 1',20)

  set nocount off;

 with Recursao (ID,PAI,MENU,HIERARQUIA)
 as
 (
   select ID,PAI,MENU,
          convert(varchar(100),'') HIERARQUIA
      from @Exemplo
      where PAI is null
   union all
   select R1.ID,
          R1.PAI,
          R1.MENU,
          case when DataLength(R2.HIERARQUIA) > 0
                    then convert(varchar(100),R2.HIERARQUIA + '.'
                                 + cast(R1.PAI as varchar(10))) 
                    else convert(varchar(100),
                                cast(R1.PAI as varchar(10))) 
                    end as HIERARQUIA
      from @Exemplo as R1
     join Recursao as R2 on R1.PAI = R2.ID
  )

select * from Recursao order by HIERARQUIA asc