Nossa, que ótimo, era isso mesmo Fabio, ajudou muito, não conhecia ROW_NUMBER(), muito util mesmo, só adaptei alguma coisinha por aqui, mas deu tudo certo,
DECLARE @CHAPA INT = 00136,
@DATAINICIAL_D DATETIME = '20101210',
@DATAFINAL_D DATETIME = '20101210'
SELECT CHAPA,
NOME,
CODSECAO,
FUNCAO,
DATA,
REPLICATE('0', 2 - LEN(CAST((SUM(ENT1) /60) AS VARCHAR))) + CAST((SUM(ENT1) /60) AS VARCHAR)+ ':' +
REPLICATE('0', 2 - LEN(CAST((SUM(ENT1) %60) AS VARCHAR))) + CAST((SUM(ENT1) %60) AS VARCHAR) AS ENT1,
REPLICATE('0', 2 - LEN(CAST((SUM(SAI1) /60) AS VARCHAR))) + CAST((SUM(SAI1) /60) AS VARCHAR)+ ':' +
REPLICATE('0', 2 - LEN(CAST((SUM(SAI1) %60) AS VARCHAR))) + CAST((SUM(SAI1) %60) AS VARCHAR) AS SAI1,
REPLICATE('0', 2 - LEN(CAST((SUM(ENT2) /60) AS VARCHAR))) + CAST((SUM(ENT2) /60) AS VARCHAR)+ ':' +
REPLICATE('0', 2 - LEN(CAST((SUM(ENT2) %60) AS VARCHAR))) + CAST((SUM(ENT2) %60) AS VARCHAR) AS ENT2,
REPLICATE('0', 2 - LEN(CAST((SUM(SAI2) /60) AS VARCHAR))) + CAST((SUM(SAI2) /60) AS VARCHAR)+ ':' +
REPLICATE('0', 2 - LEN(CAST((SUM(SAI2) %60) AS VARCHAR))) + CAST((SUM(SAI2) %60) AS VARCHAR) AS SAI2,
REPLICATE('0', 2 - LEN(CAST((SUM(ENT3) /60) AS VARCHAR))) + CAST((SUM(ENT3) /60) AS VARCHAR)+ ':' +
REPLICATE('0', 2 - LEN(CAST((SUM(ENT3) %60) AS VARCHAR))) + CAST((SUM(ENT3) %60) AS VARCHAR) AS ENT3,
REPLICATE('0', 2 - LEN(CAST((SUM(SAI3) /60) AS VARCHAR))) + CAST((SUM(SAI3) /60) AS VARCHAR)+ ':' +
REPLICATE('0', 2 - LEN(CAST((SUM(SAI3) %60) AS VARCHAR))) + CAST((SUM(SAI3) %60) AS VARCHAR) AS SAI3
FROM (SELECT CHAPA,
NOME,
CODSECAO,
FUNCAO,
DATA,
CASE WHEN SEQUENCIA = 1 THEN BATIDA ELSE 0 END ENT1,
CASE WHEN SEQUENCIA = 2 THEN BATIDA ELSE 0 END SAI1,
CASE WHEN SEQUENCIA = 3 THEN BATIDA ELSE 0 END ENT2,
CASE WHEN SEQUENCIA = 4 THEN BATIDA ELSE 0 END SAI2,
CASE WHEN SEQUENCIA = 5 THEN BATIDA ELSE 0 END ENT3,
CASE WHEN SEQUENCIA = 6 THEN BATIDA ELSE 0 END SAI3
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ABATFUN.CHAPA,
ABATFUN.DATA
ORDER BY ABATFUN.CHAPA,
ABATFUN.DATA,
ABATFUN.BATIDA) SEQUENCIA,
ABATFUN.CHAPA,
PFUNC.NOME,
PFUNC.CODSECAO,
PFUNCAO.NOME FUNCAO,
ABATFUN.DATA,
ABATFUN.BATIDA
FROM ABATFUN
INNER JOIN PFUNC ON ABATFUN.CHAPA = PFUNC.CHAPA
INNER JOIN PFUNCAO ON PFUNC.CODFUNCAO = PFUNCAO.CODIGO
WHERE ABATFUN.DATA BETWEEN @DATAINICIAL_D AND @DATAFINAL_D
AND PFUNC.CODSITUACAO <> 'D') TBPONTOLINHA
) TBPONTOCOLUNA
WHERE CHAPA = @CHAPA
AND DATA >= @DATAINICIAL_D
AND DATA <= @DATAFINAL_D
GROUP BY CHAPA,
NOME,
CODSECAO,
FUNCAO,
DATA
brigadão mesmo. Abraços.