SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tblSample (
    hier [nvarchar](255) NULL,
    lvl [nvarchar](255) NULL,
    id [int] NULL
);
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 * from tblSample


hier
lvl
id


AA00010102
3
3

AA00019999
3
4

AA00020107
3
6

AA00029999
3
7

AA11810001
3
9

AA11812087
3
10

AA11852299
3
12

AA1185
2
12

AA
1
12

AA1181
2
10

AA
1
10

AA1181
2
9

AA
1
9

AA0002
2
7

AA
1
7

AA0002
2
6

AA
1
6

AA0001
2
4

AA
1
4

AA0001
2
3

AA
1
3


select s.hier, s2.hier, s3.hier
from tblSample s join
     tblSample s2
     on s2.lvl = s.lvl + 1 and
        s2.id = s.id join
     tblSample s3
     on s3.lvl = s2.lvl + 1 and
        s3.id = s2.id
where s.lvl = 1


hier
hier
hier


AA
AA0001
AA00010102

AA
AA0001
AA00019999

AA
AA0002
AA00020107

AA
AA0002
AA00029999

AA
AA1181
AA11810001

AA
AA1181
AA11812087

AA
AA1185
AA11852299


select max(case when lvl = 1 then hier end),
       max(case when lvl = 2 then hier end),
       max(case when lvl = 3 then hier end)
from tblSample
group by id


(No column name)
(No column name)
(No column name)


AA
AA0001
AA00010102

AA
AA0001
AA00019999

AA
AA0002
AA00020107

AA
AA0002
AA00029999

AA
AA1181
AA11810001

AA
AA1181
AA11812087

AA
AA1185
AA11852299


Warning: Null value is eliminated by an aggregate or other SET operation.

hier	lvl	id
AA00010102	3	3
AA00019999	3	4
AA00020107	3	6
AA00029999	3	7
AA11810001	3	9
AA11812087	3	10
AA11852299	3	12
AA1185	2	12
AA	1	12
AA1181	2	10
AA	1	10
AA1181	2	9
AA	1	9
AA0002	2	7
AA	1	7
AA0002	2	6
AA	1	6
AA0001	2	4
AA	1	4
AA0001	2	3
AA	1	3