test

Msg 8120, Level 16, State 1, Line 19
Column 'Departments.department_hierarchy' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

-- Tworzenie tabeli z hierarchiczną strukturą departamentów
CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name NVARCHAR(100),
    parent_department_id INT,
    department_hierarchy HIERARCHYID
);

-- Wstawianie przykładowych danych
INSERT INTO Departments (department_id, department_name, parent_department_id, department_hierarchy)
VALUES
    (1, 'CEO Office', NULL, HIERARCHYID::GetRoot()),
    (2, 'Finance', 1, NULL),
    (3, 'HR', 1, NULL),
    (4, 'Accounting', 2, NULL),
    (5, 'Recruitment', 3, NULL);

-- Wstawianie danych z wykorzystaniem hierarchii już istniejących
INSERT INTO Departments (department_id, department_name, parent_department_id, department_hierarchy)
SELECT
    6,
    'Marketing',
    1,
    CASE
        WHEN parent_department_id IS NULL THEN HIERARCHYID::GetRoot().GetDescendant(CAST(0x00 AS BINARY(4)), CAST(0x01 AS BINARY(4)))
        ELSE (SELECT department_hierarchy.GetDescendant(MAX(department_hierarchy), NULL) FROM Departments WHERE department_id = parent_department_id)
    END
FROM Departments
WHERE department_id = 1; -- Wybieramy departament o ID = 1, ponieważ to on jest rodzicem dla nowego departamentu 'Marketing'

INSERT INTO Departments (department_id, department_name, parent_department_id, department_hierarchy)
SELECT
    7,
    'IT',
    1,
    CASE
        WHEN parent_department_id IS NULL THEN HIERARCHYID::GetRoot().GetDescendant(CAST(0x00 AS BINARY(4)), CAST(0x02 AS BINARY(4)))
        ELSE (SELECT department_hierarchy.GetDescendant(MAX(department_hierarchy), NULL) FROM Departments WHERE department_id = parent_department_id)
    END
FROM Departments
WHERE department_id = 1; -- Wybieramy departament o ID = 1, ponieważ to on jest rodzicem dla nowego departamentu 'IT'


-- Wyświetlanie struktury departamentów
SELECT department_id, department_name, parent_department_id, department_hierarchy.ToString() AS hierarchy_path
FROM Departments
ORDER BY department_hierarchy;