Creation query using PIVOT in SQL

Asked

Viewed 132 times

1

Consulta SQL

Dear I need to display the result of this query in columns, each row represents an employee’s point marking on the day, I’m trying to use the pivot, but I’m not getting.

sql
  • 1

    What would be the columns of the pivoting version? And the aggregation should not be by PLATE instead of BEAT?

  • The aggregation will be by plate and day, I changed the query of this image, what is picking up now are the columns, because I have to make 4 columns (input, exit meal, back meal and exit), but I do not know a way to pivot the columns.

  • Some Dbms have the PIVOT statement (Oracle, Sql Sever etc) , see the respective documentation, not having the output can be the use of CASE or IIF (search for <sql pivot case>

1 answer

1

I was able to solve it, I created a row_number to number the beats that are 4, then I used them to create the columns and used it in the pivot, it worked out, thanks.

SELECT  
CHAPA,
NOME,
SECAO,
DATA,
HORARIO_BASE,
HORAS_TRABALHADAS,
[1] ENTRADA,
[2] SAÍDA,
[3] ENTRADA,
[4] SAÍDA,      
ATRASO,
EXTRAEXECUTADO,
ABONO,
ADICIONALNOTURNO    

FROM 
(
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY ABATFUN.DATA, ABATFUN.CHAPA ORDER BY 
ABATFUN.DATA) LINHA,
        PFUNC.CHAPA,
        PFUNC.NOME,
        PSECAO.DESCRICAO SECAO,
        ABATFUN.DATA,            
        dbo.UNIFAM_CONVMIN(BATIDA) BATIDA,
        dbo.UNIFAM_CONVMIN(AAFHTFUN.BASE) HORARIO_BASE,
        dbo.UNIFAM_CONVMIN(AAFHTFUN.HTRAB) HORAS_TRABALHADAS,
        dbo.UNIFAM_CONVMIN(AAFHTFUN.ATRASO) ATRASO,
        dbo.UNIFAM_CONVMIN(AAFHTFUN.EXTRAEXECUTADO) EXTRAEXECUTADO,
        dbo.UNIFAM_CONVMIN(AAFHTFUN.ADICIONAL) ADICIONALNOTURNO,
        dbo.UNIFAM_CONVMIN(AAFHTFUN.ABONO) ABONO,
        AABONO.DESCRICAO MOTIVO_ABONO,
        CASE WHEN
            (SELECT CODAVISO FROM AAVISOCALCULADO WHERE CODCOLIGADA = 
AAFHTFUN.CODCOLIGADA AND CHAPA = AAFHTFUN.CHAPA AND DATAREFERENCIA = 
AAFHTFUN.DATA AND CODAVISO=1) = 1
            THEN 'MENOS DE 11HRS ENTRE AS JORNADAS' END INTERVALO_JORNADA,
        CASE WHEN
            (SELECT CODAVISO FROM AAVISOCALCULADO WHERE CODCOLIGADA = 
AAFHTFUN.CODCOLIGADA AND CHAPA = AAFHTFUN.CHAPA AND DATAREFERENCIA = 
AAFHTFUN.DATA AND CODAVISO=2) = 2
            THEN 'TRABALHOU MAIS DE 10HRS NO DIA' END 
TOTAL_HORAS_TRABALHADAS

    FROM 
        PFUNC   
            LEFT JOIN ABATFUN ON
            ABATFUN.CODCOLIGADA = PFUNC.CODCOLIGADA
            AND ABATFUN.CHAPA = PFUNC.CHAPA
            LEFT JOIN AAFHTFUN ON
            AAFHTFUN.CODCOLIGADA = PFUNC.CODCOLIGADA
            AND AAFHTFUN.CHAPA = PFUNC.CHAPA
            AND AAFHTFUN.DATA = ABATFUN.DATA
            INNER JOIN PSECAO ON
            PSECAO.CODCOLIGADA = PFUNC.CODCOLIGADA
            AND PSECAO.CODIGO  = PFUNC.CODSECAO
            LEFT JOIN AABONFUN ON
            AABONFUN.CODCOLIGADA = AAFHTFUN.CODCOLIGADA
            AND AABONFUN.CHAPA = AAFHTFUN.CHAPA
            AND AABONFUN.DATA = AAFHTFUN.DATA       
            LEFT JOIN AABONO ON
            AABONO.CODCOLIGADA = AABONFUN.CODCOLIGADA
            AND AABONO.CODIGO = AABONFUN.CODABONO   
    WHERE 
        PFUNC.CODCOLIGADA=1
        AND PFUNC.CODFILIAL=2
        AND PFUNC.CODTIPO<>'A'
        --AND PFUNC.CHAPA='200739' 
        AND ABATFUN.DATA BETWEEN '2018-03-21' AND '2018-04-20'

)TABELACONSULTA

PIVOT

(
    MAX(BATIDA)
    FOR [LINHA] IN ([1],[2],[3],[4])
) 
AS TABELAPIVOT


ORDER BY
DATA
,NOME
  • 1

    I could post the part you changed in the code?

  • Nice to collaborate with the answer, as @Don'tPanic asked, otherwise this answer can be deleted because it has little content.

  • I put the code in the statement that I was able to do and answered what I needed.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.