How to turn this query into a Pivot?

Question

If theres something I can't learn, is PIVOT.

People are trying to teach me this, I'm reading blogs about this but I just can't understand the logic.

How can I turn this query , into a pivot?

The query:

select 
    case datename(weekday,Vit_DataAberturaReal) 
        when 'Monday' then 'Segunda-Feira'
        when 'Tuesday' then 'Terça-Feira'
        when 'wednesday' then 'Quarta-Feira'
        when 'Thursday' then 'Quinta-Feira'
        when 'Friday' then 'Sexta-Feira'
        when 'Saturday' then 'Sábado'
        when 'Sunday' then 'Domingo' end as 'Dia_Da_Semana',
    case datepart(hour,Vit_DataAberturaReal) 
        when '0' then '00:00'
        when '1' then '01:00' 
        when '2' then '02:00' 
        when '3' then '03:00' 
        when '4' then '04:00' 
        when '5' then '05:00' 
        when '6' then '06:00' 
        when '7' then '07:00' 
        when '8' then '08:00' 
        when '9' then '09:00' 
        when '10' then '10:00' 
        when '11' then '11:00' 
        when '12' then '12:00' 
        when '13' then '13:00' 
        when '14' then '14:00' 
        when '15' then '15:00' 
        when '16' then '16:00' 
        when '17' then '17:00' 
        when '18' then '18:00' 
        when '19' then '19:00' 
        when '20' then '20:00' 
        when '21' then '21:00' 
        when '22' then '22:00' 
        when '23' then '23:00' 
        when '24' then '24:00'  end as Hora,
    count(Vit_DataAberturaReal) as Count_Reps_Abertas
from tb_Vitima 
where Vit_DataAberturaReal between '2016-01-03 00:00:00.000' and '2016-01-16 23:59:59.000'
group by datename(weekday,Vit_DataAberturaReal),datepart(hour,Vit_DataAberturaReal)
order by 1,2

This is the result:

And this is what I want:

Answer 1

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.

Answer 2

Although this has been answered, I wanted to point out a couple of things.

Firstly, there is a SET LANGUAGE command which will save you a lot of typing.

set langauge Portugeuse;

This will localise the DATENAME function so you don't have to manually translate.

Secondly, you are using SQL 2014, which has a format function (although it is slower than your case statement, it is worth knowing it is there)

select format(getdate(), 'HH:mm'); -- 24 Hour time

Thirdly, PIVOT can be very slow on large sets, so another construct you can use is this:

SET LANGUAGE Portuguese;
SELECT 
      DATEPART(DW, [Vit_DataAberturaReal]) AS [DW] -- For ORDERING
    , DATENAME(DW, [Vit_DataAberturaReal]) AS [Dia_Da_Semana]
    , SUM( CASE WHEN DATEPART(HOUR, [Vit_DataAberturaReal]) = 0 THEN 1 END ) AS [00:00]
    , SUM( CASE WHEN DATEPART(HOUR, [Vit_DataAberturaReal]) = 1 THEN 1 END ) AS [02:00]
    , SUM( CASE WHEN DATEPART(HOUR, [Vit_DataAberturaReal]) = 2 THEN 1 END ) AS [03:00]
    , SUM( CASE WHEN DATEPART(HOUR, [Vit_DataAberturaReal]) = 3 THEN 1 END ) AS [04:00]
    , SUM( CASE WHEN DATEPART(HOUR, [Vit_DataAberturaReal]) = 4 THEN 1 END ) AS [05:00]    
    -- Keep repeating these columns to include all hours of the day
    FROM tb_Vitima 
    GROUP BY DATENAME(DW, [Vit_DataAberturaReal]),DATEPART(DW, [Vit_DataAberturaReal])
    ORDER BY DW