USING PIVOT ORACLE

Question

USING PIVOT ORACLE

Navigation

#1 by (1 votes)

2

I have this 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 'DEC' column = 2 for the Year = 2016 and the month = 3, however, it is showing me as 'DEC' = 1.

Can anyone help me?

oracle pivot

asked by anonymous 28.07.2017 / 17:12

1 answer

Mysql Database Synchronization between Servers (VPS) Share internet via wifi using command line [closed]

score 1 · Accepted Answer

This behavior is caused by using GROUP BY in the sub-query. The way it is being used behaves similarly to using the DISTINCT predicate. Your 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'll 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
;