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 VARCHAR(10),
    column_user_name VARCHAR(50),
    column_cases INT,
    column_number_hour INT,
    column_number_index INT
)

INSERT table_cases
VALUES
    ('AAA111', 'Shelby, Marie',  5,  9, 0),
    ('BBB222', 'Wayne, Johnny', 15, 10, 1),
    ('BBB222', 'Wayne, Johnny', 51,  9, 0)

-- 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
AAA111	Shelby, Marie	0	5
BBB222	Wayne, Johnny	0	51
BBB222	Wayne, Johnny	1	15

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
AAA111	Shelby, Marie	5	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null
BBB222	Wayne, Johnny	51	15	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null