Hierarchy - services, Sql Server

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

create table service_area
(AreaID int, Service varchar(2))

create table service
(
service varchar(2),
description varchar(3),
level1 varchar(2),
level2 varchar(2),
level3 varchar(2)
)

insert into service_area values
(1,'A1'),
(2,'D2')

insert into service values
('A1','AA1','A2','B1','C1'),
('A2','AA2','A3',NULL,NULL),
('A3','AA3','A4',NULL, NULL),
('A4','AA4',NULL,NULL,NULL),
('B1','BB1',NULL,'B2',NULL),
('B2','BB2',NULL,'B3',NULL),
('B3','BB3',NULL,NULL,NULL),
('C1','CC1',NULL,NULL,'C2'),
('C2','CC2',NULL,NULL,NULL)

-- Creation of the pivot table 
SELECT *
into pivote 
FROM 
service t
UNPIVOT
(
 LEVEL_DESC FOR LEVEL IN (LEVEL1, LEVEL2, LEVEL3)
) AS t2

;
-- Recursive code 
WITH levels AS
(
	SELECT s.areaid, a.*, 1 AS LEVEL_NRO 
	FROM pivote a 
	inner join service_area s 
	ON a.service = s.service
	UNION ALL
	SELECT l.AreaID,p.*, 1+LEVEL_NRO AS LEVEL_NRO FROM pivote p 
	INNER JOIN levels L ON l.LEVEL_DESC = p.service
)

SELECT AreaID,service, description,LEVEL,LEVEL_DESC, LEVEL_NRO 
INTO final_table
FROM levels
ORDER BY  LEVEL, service

-- adding the null registers eliminated by the pivot instruction
insert into final_table  
select f.AreaID, s.service, s.description,
f.LEVEL, null, null
from service s 
inner join final_table f on s.service = f.LEVEL_DESC
where 
level1 is null and 
level2 is null and 
level3 is null

-- Correcting the Level Nro (descending)
update final_table 
set LEVEL_NRO = a.LEVEL_NRO +2 - ISNULL(final_table.LEVEL_NRO,a.LEVEL_NRO +1) 
from 
(select AreaID, LEVEL, MAX(LEVEL_NRO) as level_nro
from final_table
group by AreaID, LEVEL) a 
where final_table.AreaID = a.AreaID
and final_table.LEVEL = a.LEVEL

SELECT AreaID, service,
description, LEVEL, LEVEL_NRO FROM final_table
ORDER BY  LEVEL, service, LEVEL_NRO desc

View schema
Execution time: 0,08 sec, rows selected: 11, rows affected: 25, absolute service time: 0,25 sec, absolute service time: 0,25 sec