Turn row into columns using pivot

Question

I have this list that is the result of the query below:

This is the query:

SELECT PERIODO, ST_NOME_SUPERVISOR, Sum (NU_TOTAL_VENDA) AS NU_TOTAL_VENDA
FROM (
      SELECT
        UPAI.ST_NOME AS ST_NOME_SUPERVISOR,
        To_Char(A.DT_CRIADO_EM,'HH24') || ':00' AS PERIODO,
        CASE A.NU_SITUACAO_SUBSTATUS
          WHEN 2 THEN 1
          WHEN 3 THEN 1
          ELSE 0
        END AS NU_TOTAL_VENDA
      FROM TB_ATENDIMENTO A
        INNER JOIN TB_USUARIO U ON (U.ID_USUARIO = A.ID_CRIADO_POR)
          LEFT JOIN TB_USUARIO UPAI ON (UPAI.ID_USUARIO = U.NU_CODIGO_PAI)
      WHERE A.DT_CRIADO_EM >= To_Date('05/10/2020 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
            AND A.DT_CRIADO_EM <= To_Date('05/10/2020 23:59:59', 'DD/MM/YYYY HH24:MI:SS')
            AND A.ID_OPERACAO = 8
            AND (U.NU_CODIGO_PAI IN(4365,4366)
                                    OR Nvl(0,'0') = '0'
                                  )
    )
GROUP BY PERIODO, ST_NOME_SUPERVISOR
UNION
SELECT LPad(PERIODO, 2, '0') || ':00' AS PERIODO, NULL AS ST_NOME_SUPERVISOR, 0 AS NU_TOTAL_VENDA
FROM (
      WITH DEMO AS (SELECT 0 AS "0", 0 AS "1", 0 AS "2", 0 AS "3", 0 AS "4", 0 AS "5", 0 AS "6", 0 AS "7", 0 AS "8", 0 AS "9", 0 AS "10", 0 AS "11", 0 AS "12", 0 AS "13", 0 AS "14", 0 AS "15", 0 AS "16", 0 AS "17", 0 AS "18", 0 AS "19", 0 AS "20", 0 AS "21", 0 AS "22", 0 AS "23" FROM DUAL )
      SELECT * FROM DEMO
      UNPIVOT
      ( VALOR FOR PERIODO 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") )
      )
WHERE PERIODO BETWEEN 8 AND 20
AND PERIODO NOT IN (
                      SELECT To_Number(To_Char(A.DT_CRIADO_EM,'HH24'))
                      FROM TB_ATENDIMENTO A
                        INNER JOIN TB_USUARIO U ON (U.ID_USUARIO = A.ID_CRIADO_POR)
                          LEFT JOIN TB_USUARIO UPAI ON (UPAI.ID_USUARIO = U.NU_CODIGO_PAI)
                      WHERE A.DT_CRIADO_EM >= To_Date('05/10/2020 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
                        AND A.DT_CRIADO_EM <= To_Date('05/10/2020 23:59:59', 'DD/MM/YYYY HH24:MI:SS')
                        AND A.ID_OPERACAO = 8
                        AND (U.NU_CODIGO_PAI IN(4365,4366)
                                                OR Nvl(0,'0') = '0'
                                              )
                      GROUP BY To_Char(A.DT_CRIADO_EM,'HH24')
                      )
ORDER BY PERIODO, ST_NOME_SUPERVISOR ;

Is it possible to turn rows into columns by applying the pivot ? I even did as you can notice, but now it’s complex, I’ve tried and I’m a couple of days in this query and nothing, someone can help me ?
the List should look like this:

Answer 1

How about the following solution:

WITH 
    HOURS AS (
        SELECT LEVEL AS PERIOD
        FROM dual
        CONNECT BY LEVEL < 24
),
    TABLE_A AS (
        SELECT
            UPAI.ST_NOME AS ST_NOME_SUPERVISOR,
            To_Char(A.DT_CRIADO_EM,'HH24') || ':00' AS PERIODO,
            CASE A.NU_SITUACAO_SUBSTATUS
            WHEN 2 THEN 1
            WHEN 3 THEN 1
            ELSE 0
            END AS NU_TOTAL_VENDA
        FROM TB_ATENDIMENTO A
            INNER JOIN TB_USUARIO U ON (U.ID_USUARIO = A.ID_CRIADO_POR)
            LEFT JOIN TB_USUARIO UPAI ON (UPAI.ID_USUARIO = U.NU_CODIGO_PAI)
        WHERE TRUNC(A.DT_CRIADO_EM) = To_Date('05/10/2020 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
          AND A.ID_OPERACAO = 8
          AND (U.NU_CODIGO_PAI IN (4365, 4366)
               OR Nvl(0, '0') = '0'
          )
),
    TABLE_B AS (
        SELECT TO_NUMBER(To_Char(A.DT_CRIADO_EM,'HH24')) TIME_B
        FROM TABLE_A
        GROUP BY To_Char(A.DT_CRIADO_EM,'HH24')
),
    FINAL_TABLE AS (
        SELECT PERIODO, ST_NOME_SUPERVISOR, SUM(NU_TOTAL_VENDA) AS NU_TOTAL_VENDA
        FROM (
            SELECT *
            FROM TABLE_A
            )
        GROUP BY PERIODO, ST_NOME_SUPERVISOR
        UNION
        SELECT LPAD(PERIODO, 2, '0') || ':00' AS PERIODO, NULL AS ST_NOME_SUPERVISOR, 0 AS NU_TOTAL_VENDA
        FROM (
            SELECT * FROM HOURS
        )
        WHERE PERIODO BETWEEN 8 AND 20
        AND PERIODO NOT IN (SELECT TIME_B FROM TABLE_B)
        ORDER BY PERIODO, ST_NOME_SUPERVISOR
)
SELECT *
FROM (
    SELECT *
    FROM FINAL_TABLE
) PIVOT (SUM(NU_TOTAL_VENDA) FOR  ST_NOME_SUPERVISOR IN ('LETICIA', 'ALFREDO', 'CARLOS', (...)))
ORDER BY 1
;

I made some changes to simplify the original search and make everything more readable.

The first is to avoid repeating searches. The table referred to with TABLE_A is the basis for subsequent searches. Referenced with a WITH the search becomes smaller and readable.

The second is how to restrict the period (initially with a recourse to UNPIVOT) got smaller.

Finally, the requested scale is made using the clause PIVOT. The intended names must be listed one by one. Even if everyone wants to be listed what can be boring but, for the normal format, Oracle does not allow the use of * for the enumeration of the columns in PIVOT (but for the format XML already allows it using keyword ANY - Go and see why).