Improve a query with Pivot and Recursive code in SQL Server

Question

I need to reach the next result considering these two tables.

An area receives services from different departments. Each department belongs to a hierarchy on three (or fewer) levels. The idea is to represent in one column the relationship between the area and all the hierarchies where it can be present. The Level Nro should be 1 for the record that does not have any father.

So far, I have this code https://rextester.com/KYHKR17801 . I've got the result that I need. However, the performance is not the best because the table is too large, and I had to do many transformations:

• Pivot
• Recursion
• Addition of register because I lost the nulls when creating the Pivot table
• Update the level Nro

I do not if anyone can give any advice to improve the runtime of this query.

Answer 1

This appears to do everything you need in one statement:

WITH R AS
(
    SELECT 
        SA.AreaID, 
        S.[service], 
        S.[description], 
        L.[Level], 
        L.child_service,
        Recursion = 1
    FROM dbo.service_area AS SA
    JOIN dbo.[service] AS S
        ON S.[service] = SA.[Service]
    OUTER APPLY
    (
        -- Unpivot
        VALUES
            (1, S.level1),
            (2, S.level2),
            (3, S.level3)
    ) AS L ([Level], child_service)
    WHERE
        L.child_service IS NOT NULL

    UNION ALL

    SELECT 
        R.AreaID, 
        S.[service], 
        S.[description], 
        R.[Level], 
        child_service = CHOOSE(R.[Level], S.level1, S.level2, S.level3),
        Recursion = R.Recursion + 1
    FROM R
    JOIN dbo.[service] AS S
        ON S.[service] = R.child_service
)
SELECT
    R.AreaID, 
    R.[service],
    R.[description], 
    [Level] = 'Level' + CONVERT(char(1), R.[Level]), 
    [Level Nro] = ROW_NUMBER() OVER (
        PARTITION BY R.AreaID, R.[Level] 
        ORDER BY R.Recursion DESC)
FROM R
ORDER BY 
    R.AreaID ASC,
    R.[Level] ASC, 
    [Level Nro]
OPTION (MAXRECURSION 3);

The following index will help the recursive section locate rows quickly:

CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.[service] ([service]);

db<>fiddle demo

If your version of SQL Server doesn't have CHOOSE, write the CASE statement out by hand:

CASE R.[Level] WHEN 1 THEN S.level1 WHEN 2 THEN S.level2 ELSE S.level3 END