SQL Server 2019

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

CREATE TABLE [dbo].[COMPANY](
    [COMPANY_KEY] [int] NOT NULL,
    [COMPANY_NAME] [varchar](50) NULL,
    [PARENT_KEY] [int] NULL
) 
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (100, N'MICROSOFT', NULL)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (101, N'SOFTWARE', 100)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (102, N'CONSULTING', 101)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (103, N'PRODUCTS', 101)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (104, N'OFFICE', 103)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (105, N'VISIO', 104)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (106, N'VISIO EUROPE', 105)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (107, N'BACK OFFICE', 103)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (108, N'SQL SERVER', 107)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (109, N'OLAP SERVICES', 108)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (110, N'DTS', 108)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (111, N'REPOSITORY', 108)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (112, N'DEVELOPER TOOLS', 103)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (113, N'WINDOWS', 103)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (114, N'ENTERTAINMENT', 103)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (115, N'GAMES', 114)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (116, N'MULTIMEDIA', 114)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (117, N'EDUCATION', 101)
18 rows affected

select * from company


COMPANY_KEY
COMPANY_NAME
PARENT_KEY


100
MICROSOFT
null

101
SOFTWARE
100

102
CONSULTING
101

103
PRODUCTS
101

104
OFFICE
103

105
VISIO
104

106
VISIO EUROPE
105

107
BACK OFFICE
103

108
SQL SERVER
107

109
OLAP SERVICES
108

110
DTS
108

111
REPOSITORY
108

112
DEVELOPER TOOLS
103

113
WINDOWS
103

114
ENTERTAINMENT
103

115
GAMES
114

116
MULTIMEDIA
114

117
EDUCATION
101


with cte as (
       select company_key, company_name, company_key as parent_key, 0 as lev
       from company
       where company_key in (103, 107)
       union all
       select c.company_key, c.company_name, cte.parent_key, 1 + cte.lev
       from cte join
            company c
            on cte.company_key = c.parent_key
      )
select * 
from cte
order by parent_key, lev


company_key
company_name
parent_key
lev


103
PRODUCTS
103
0

104
OFFICE
103
1

107
BACK OFFICE
103
1

112
DEVELOPER TOOLS
103
1

113
WINDOWS
103
1

114
ENTERTAINMENT
103
1

115
GAMES
103
2

116
MULTIMEDIA
103
2

108
SQL SERVER
103
2

105
VISIO
103
2

106
VISIO EUROPE
103
3

109
OLAP SERVICES
103
3

110
DTS
103
3

111
REPOSITORY
103
3

107
BACK OFFICE
107
0

108
SQL SERVER
107
1

109
OLAP SERVICES
107
2

110
DTS
107
2

111
REPOSITORY
107
2

COMPANY_KEY	COMPANY_NAME	PARENT_KEY
100	MICROSOFT	null
101	SOFTWARE	100
102	CONSULTING	101
103	PRODUCTS	101
104	OFFICE	103
105	VISIO	104
106	VISIO EUROPE	105
107	BACK OFFICE	103
108	SQL SERVER	107
109	OLAP SERVICES	108
110	DTS	108
111	REPOSITORY	108
112	DEVELOPER TOOLS	103
113	WINDOWS	103
114	ENTERTAINMENT	103
115	GAMES	114
116	MULTIMEDIA	114
117	EDUCATION	101