How to Convert Oracle to MS SQL Server - PIVOT and STRING_AGG

Please help me convert Oracle into SQL Server PIVOT.

The table_cases contains these data:

And the expected result is below. Note: I just short-cut the columns "hour-Nth" for "hour-01" to "hour-24" columns.

Oracle:

SELECT 
  "column_user_id", 
  "column_user_name", 
  "column_cases", 
  "column_number_hour", 
  "column_number_index"
FROM 
  table_cases 
  /* Use Pivot to flip the time windows */
  PIVOT (
    listagg(column_cases, ',') within group(
      order by 
        column_number_hour
    ) for column_number_index in (
      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"
    )
  )

When I tried to convert it to SQL Server like this. I made a temporary table for this example only if it helps understand my question.

CREATE TABLE #table_cases (
    column_user_id nvarchar(10),
    column_user_name nvarchar(50),
    column_cases int,
    column_number_hour int,
    column_index_hour int
)

INSERT INTO #table_cases (column_user_id, column_user_name, column_cases, column_number_hour, column_index_hour)
VALUES ('AAA111', 'Shelby, Marie', 5, 9, 0); 

INSERT INTO #table_cases (column_user_id, column_user_name, column_cases, column_number_hour, column_index_hour)
VALUES ('BBB222', 'Wayne, Johnny', 15, 10, 1); 

INSERT INTO #table_cases (column_user_id, column_user_name, column_cases, column_number_hour, column_index_hour)
VALUES ('BBB222', 'Wayne, Johnny', 51, 9, 0); 

SELECT * 
FROM 
    (SELECT 
         "column_user_id", 
         "column_user_name", 
         "column_cases", 
         "column_number_hour", 
         "column_number_index"
     FROM 
         #table_cases) AS TABLE_1
  /* Use Pivot to flip the time windows */
 PIVOT (
    STRING_AGG( TABLE_1."column_cases", ';') WITHIN GROUP (ORDER BY TABLE_1."column_number_hour")  
    for TABLE_1.column_number_index in (
      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" 
    )
  ) AS PIVOT_TABLE_1

I get the following errors:

Error: Incorrect syntax near ';'

The ORDER BY on line ORDER BY TABLE_1."column_number_hour" also shows an error.

The "0" on the line -> 0 as "Hour-01" also throws an error.

In Oracle the function "listagg" works well in the PIVOT function. But in SQL Server, can we use STRING_AGG inside the PIVOT as aggregate function?

Because it seems to get error. The intention to put alias in the column_number_index "in" also shows error, would it be possible?

You are close. The two issues I see are (1) SQL Server PIVOT does not support the STRING_AGG() function and (2) SQL Server does not allow direct aliasing of the values in the FOR clause of the PIVOT.

The first issue can be resolved by performing a GROUP BY and STRING_AGG() separately and then feed that into the PIVOT. The second issue can be resolved by aliasing the PIVOT columns in the final select.

Something like:

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

Note that the above uses [quoted] style identifiers where needed, as seems to be more customary in SQL server, but "quoted" style identifiers can also be used.

Sample results:

See this db<>fiddle for a working example.

(Side note: Check your usage of column_number_index vs column_number_hour. The current usage just seemed odd to me.)