10. hierarchyid 4

CREATE TABLE Renewable_Energy_Devices (
    device_id NVARCHAR(26) PRIMARY KEY,
    device_status INT CHECK (device_status >= 0 AND device_status <= 6),
    device_power DECIMAL(18,0),
    company_id INT,
    device_location GEOGRAPHY,
    device_location_geom GEOMETRY,
    device_hierarchy HIERARCHYID,
    device_description XML
);

DECLARE @latitude DECIMAL(9, 6), @longitude DECIMAL(9, 6);
DECLARE @point GEOMETRY;
DECLARE @hierarchy HIERARCHYID;
DECLARE @device_description XML;

DECLARE @i INT = 1;
WHILE @i <= 50
BEGIN
    SET @latitude = RAND() * 180 - 90;
    SET @longitude = RAND() * 360 - 180;
    SET @point = GEOMETRY::Point(@latitude, @longitude, 4326);
    SET @hierarchy = HIERARCHYID::GetRoot();
    SET @device_description = '<description>Device_' + CAST(@i AS NVARCHAR(5)) + '</description>'; -- Zmiana sposobu tworzenia wartości dla kolumny device_description

    INSERT INTO Renewable_Energy_Devices (device_id, device_status, device_power, company_id, device_location, device_location_geom, device_hierarchy, device_description)
    VALUES (
        'Device_' + CAST(@i AS NVARCHAR(5)),
        CAST(RAND() * 6 AS INT),
        CAST(RAND() * 1000 AS DECIMAL(18, 0)),
        CAST(RAND() * 10 AS INT) + 1,
        GEOGRAPHY::Point(@latitude, @longitude, 4326),
        @point,
        @hierarchy.GetDescendant(NULL, NULL),
        @device_description
    );

    SET @i = @i + 1;
END;

WITH DeviceHierarchyCTE AS (
    SELECT
        device_id,
        device_hierarchy,
        CAST(device_id AS NVARCHAR(MAX)) AS hierarchy_path,
        CAST(device_id AS NVARCHAR(MAX)) AS device_path,
        1 AS hierarchy_level
    FROM
        Renewable_Energy_Devices
    WHERE
        device_hierarchy = HIERARCHYID::GetRoot()

    UNION ALL

    SELECT
        red.device_id,
        red.device_hierarchy,
        CAST(dhc.hierarchy_path AS NVARCHAR(MAX)).GetDescendant(red.device_hierarchy, NULL),
        dhc.device_path + ' > ' + red.device_id,
        dhc.hierarchy_level + 1
    FROM
        Renewable_Energy_Devices AS red
    INNER JOIN
        DeviceHierarchyCTE AS dhc ON CAST(dhc.hierarchy_path AS NVARCHAR(MAX)).IsDescendantOf(red.device_hierarchy) = 1
)
SELECT
    device_id,
    device_hierarchy.ToString() AS hierarchy_path,
    device_path,
    hierarchy_level
FROM
    DeviceHierarchyCTE
ORDER BY
    hierarchy_path;