How to turn this query into a Pivot?
The PIVOT synxtax is:

WITH query as (
    your query...
)
SELECT Dia_Da_Semana, [00:00], [01:00], [02:00], ...
FROM query
PIVOT (
    MAX(Count_Reps_Abertas)
    FOR Hora IN ([00:00], [01:00], [02:00], ...)
) as piv
;


You can refer to: Using PIVOT and UNPIVOT

Your query goes to the query CTE. This query/CTE must return 3 columns:


row name = [Dia_Da_Semana]
column name = Hora 
value = Count_Reps_Abertas


The SELECT and the FOR ... IN(...) must contain a list of all the rows to be pivoted into columns.

Why is there a MAX? Pivot needs an aggregate function such as count, sum, max or min. This is because you could have several rows for each pair of name and month and need to group their values. You have only 1 row for each pair of name and hour but you still have to use it although there is only 1 value to add or max. Therefore you could use either of max, min or sum.