USING PIVOT ORACLE

Question

I have that result:

Ana DEC 123 7 2016 1GG

Ana DEC 123 3 2016 1GG

Ana DEC 123 3 2016 1GG

Ana JCM 123 5 2017 1GG

Edson DES 123 11 2016 1GG

Edson DES 123 3 2017 1GG

I’m using this PLSQL:

SELECT
    *
FROM
(
    SELECT
        C.DS_MAGISTRADO,
        TIPO_ATO,
        NR_PROCESSO,
        EXTRACT(MONTH FROM DT_REGISTRO) MES,
        EXTRACT(YEAR FROM DT_REGISTRO) ANO,
        B.DS_SISTEMA
    FROM  
        PROD_ATO A
        INNER JOIN REF_SISTEMA B ON A.ID_SISTEMA = B.ID_SISTEMA
        INNER JOIN REF_MAGISTRADO C ON A.ID_MAGISTRADO = C.ID_MAGISTRADO
    WHERE
        EXTRACT(YEAR FROM DT_REGISTRO) IN (2016, 2017)
        AND NR_PROCESSO = '123'

    GROUP BY
        C.DS_MAGISTRADO,
        TIPO_ATO,
        NR_PROCESSO,
        EXTRACT(YEAR FROM DT_REGISTRO),
        EXTRACT(MONTH FROM DT_REGISTRO),
        B.DS_SISTEMA
)
PIVOT
    (COUNT(TIPO_ATO) FOR TIPO_ATO IN ('DEC', 'DES', 'EXT', 'HOM', 'JCM', 'JSM'))
ORDER BY
    DS_MAGISTRADO,
    MES,
    ANO;    

The result should be in the column 'DEC' = 2 for the Year = 2016 and the month = 3, however, is showing me as 'DEC' = 1.

Someone can help me?

Answer 1

This behavior is caused by the use of GROUP BY in the sub-query. The way it is being used has a behavior similar to the use of the DISTINCT predicate. Its sub-query is returning

Ana   DEC 123  7 2016 1GG 
Ana   DEC 123  3 2016 1GG 
Ana   JCM 123  5 2017 1GG 
Edson DES 123 11 2016 1GG
Edson DES 123  3 2017 1GG

instead of

Ana   DEC 123  7 2016 1GG 
Ana   DEC 123  3 2016 1GG 
Ana   DEC 123  3 2016 1GG
Ana   JCM 123  5 2017 1GG 
Edson DES 123 11 2016 1GG
Edson DES 123  3 2017 1GG

Try removing the aggregation and you will see that you get the expected result.

SELECT  *
FROM
(
    SELECT C.DS_MAGISTRADO,
           TIPO_ATO,
           NR_PROCESSO,
           EXTRACT(MONTH FROM DT_REGISTRO) MES,
           EXTRACT(YEAR FROM DT_REGISTRO) ANO,
           B.DS_SISTEMA
    FROM  
        PROD_ATO A
        INNER JOIN REF_SISTEMA B ON A.ID_SISTEMA = B.ID_SISTEMA
        INNER JOIN REF_MAGISTRADO C ON A.ID_MAGISTRADO = C.ID_MAGISTRADO
    WHERE EXTRACT(YEAR FROM DT_REGISTRO) IN (2016, 2017)
      AND NR_PROCESSO = '123'
)
PIVOT 
(
    COUNT(TIPO_ATO) FOR TIPO_ATO IN ('DEC', 'DES', 'EXT', 'HOM', 'JCM', 'JSM')
) Piv
ORDER BY DS_MAGISTRADO, MES, ANO
;