SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
CREATE TABLE [tblSample](
    [hier] [nvarchar](255) NULL,
    [lvl] [nvarchar](255) NULL,
    [id] [int] NULL
) ON [PRIMARY];
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA00010102', N'3', 3);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA00019999', N'3', 4);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA00020107', N'3', 6);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA00029999', N'3', 7);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA11810001', N'3', 9);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA11812087', N'3', 10);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA11852299', N'3', 12);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA1185', N'2', 12);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA', N'1', 12);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA1181', N'2', 10);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA', N'1', 10);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA1181', N'2', 9);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA', N'1', 9);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA0002', N'2', 7);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA', N'1', 7);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA0002', N'2', 6);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA', N'1', 6);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA0001', N'2', 4);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA', N'1', 4);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA0001', N'2', 3);
INSERT [tblSample] ([hier], [lvl], [id]) VALUES (N'AA', N'1', 3);
21 rows affected

SELECT t1.hier, t2.hier, t3.hier 
  FROM tblSample t1 
 INNER JOIN tblSample t2 
    ON t1.id=t2.id 
   AND t2.lvl=t1.lvl+1 
 INNER JOIN tblSample t3 
    ON t1.id=t3.id 
   AND t3.lvl=t1.lvl+2


hier
hier
hier


AA
AA1185
AA11852299

AA
AA1181
AA11812087

AA
AA1181
AA11810001

AA
AA0002
AA00029999

AA
AA0002
AA00020107

AA
AA0001
AA00019999

AA
AA0001
AA00010102


select id
      ,[1] as hier1
      ,[2] as hier2
      ,[3] as hier3
from 
(
  select t1.hier,t1.lvl,t1.id
  from tblsample t1
) src
pivot
(
  max(hier)
  for lvl in ([1], [2], [3])
) piv;


id
hier1
hier2
hier3


3
AA
AA0001
AA00010102

4
AA
AA0001
AA00019999

6
AA
AA0002
AA00020107

7
AA
AA0002
AA00029999

9
AA
AA1181
AA11810001

10
AA
AA1181
AA11812087

12
AA
AA1185
AA11852299
hier	hier	hier
AA	AA1185	AA11852299
AA	AA1181	AA11812087
AA	AA1181	AA11810001
AA	AA0002	AA00029999
AA	AA0002	AA00020107
AA	AA0001	AA00019999
AA	AA0001	AA00010102