SQL Server 2022

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

CREATE Table table_cases (
    column_user_id INT,
    column_user_name VARCHAR(10),
    column_cases VARCHAR(10),
    column_number_hour INT,
    column_number_index INT
)

INSERT table_cases
VALUES
    (1, 'User1', 'Red',     1, 0),
    (1, 'User1', 'Blue',    2, 0),
    (1, 'User1', 'Green',   2, 1),
    (1, 'User1', 'Yellow',  1, 1),
    (2, 'User2', 'Black',   4, 23),
    (2, 'User2', 'Cyan',    1, 23),
    (2, 'User2', 'Magenta', 2, 23),
    (2, 'User2', 'Yellow',  3, 23)

-- Intermediate aggregated results
SELECT
    column_user_id, 
    column_user_name,
    column_number_index,
    STRING_AGG(TABLE_1.column_cases, ';')
        WITHIN GROUP (ORDER BY TABLE_1.column_number_hour)
        AS aggregated_cases
FROM (
    SELECT 
        column_user_id, 
        column_user_name, 
        column_cases, 
        column_number_hour, 
        column_number_index
    FROM table_cases
) AS TABLE_1
GROUP BY column_user_id, column_user_name, column_number_index
ORDER BY column_user_id, column_user_name, column_number_index

-- Aggregated, pivoted, and aliased results
SELECT
    column_user_id, 
    column_user_name,
    [0] as "Hour-01",  [1] as "Hour-02",  [2] as "Hour-03", 
    [3] as "Hour-04",  [4] as "Hour-05",  [5] as "Hour-06", 
    [6] as "Hour-07",  [7] as "Hour-08",  [8] as "Hour-09", 
    [9] as "Hour-10",  [10] as "Hour-11", [11] as "Hour-12", 
    [12] as "Hour-13", [13] as "Hour-14", [14] as "Hour-15", 
    [15] as "Hour-16", [16] as "Hour-17", [17] as "Hour-18", 
    [18] as "Hour-19", [19] as "Hour-20", [20] as "Hour-21", 
    [21] as "Hour-22", [22] as "Hour-23", [23] as "Hour-24" 
FROM (
    SELECT
        TABLE_1.column_user_id, 
        TABLE_1.column_user_name,
        TABLE_1.column_number_index,
        STRING_AGG(TABLE_1.column_cases, ';')
            WITHIN GROUP (ORDER BY TABLE_1.column_number_hour)
            AS aggregated_cases
    FROM (
        SELECT 
            column_user_id, 
            column_user_name, 
            column_cases, 
            column_number_hour, 
            column_number_index
        FROM table_cases
    ) AS TABLE_1
    GROUP BY column_user_id, column_user_name, column_number_index
) G
PIVOT (
    MAX(G.aggregated_cases)  
    FOR G.column_number_index IN (
        [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
        [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23]
    )
) AS PIVOT_TABLE_1

column_user_id	column_user_name	column_number_index	aggregated_cases
1	User1	0	Red;Blue
1	User1	1	Yellow;Green
2	User2	23	Cyan;Magenta;Yellow;Black

column_user_id	column_user_name	Hour-01	Hour-02	Hour-03	Hour-04	Hour-05	Hour-06	Hour-07	Hour-08	Hour-09	Hour-10	Hour-11	Hour-12	Hour-13	Hour-14	Hour-15	Hour-16	Hour-17	Hour-18	Hour-19	Hour-20	Hour-21	Hour-22	Hour-23	Hour-24
1	User1	Red;Blue	Yellow;Green	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null
2	User2	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	Cyan;Magenta;Yellow;Black