Fórum Incrementando um Pivot #620803

CREATE OR ALTER FUNCTION [dbo].[SplitStrings]
(
   @List NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
						 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
						 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
	   E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
	   E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
	   E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
	   cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1)))
						 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
	   cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
						 WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
	FROM cteStart s;

GO

CREATE OR ALTER FUNCTION fn_COLUNAS_PIVOT
(
	@CODCOLIGADA_I AS INTEGER,
	@ANOCOMP_I AS INTEGER,
	@MESCOMP_I AS INTEGER
) RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @RETORNO AS VARCHAR (MAX)

	SET @RETORNO = 
		STUFF((
				SELECT DISTINCT ',' + AMOVFUN.CODEVE
				FROM AMOVFUN 
				INNER JOIN PFUNC ON PFUNC.CODCOLIGADA = AMOVFUN.CODCOLIGADA AND PFUNC.CHAPA = AMOVFUN.CHAPA
				     
				WHERE AMOVFUN.CODCOLIGADA = @CODCOLIGADA_I
				AND AMOVFUN.ANOCOMP = @ANOCOMP_I 
				AND AMOVFUN.MESCOMP = @MESCOMP_I 
				AND PFUNC.CODSITUACAO <> 'D'

		FOR XML PATH('')), 1, 1, '')
	
	RETURN @RETORNO

 END
 
 GO

DECLARE @CODCOLIGADA_I AS INTEGER = 1
DECLARE @ANOCOMP_I AS INTEGER = 2022
DECLARE @MESCOMP_I AS INTEGER = 3

SELECT * FROM (

	 SELECT AMOVFUN.CHAPA
				  , PFUNC.NOME
				  , PFUNC.CODSITUACAO
				  ,	PFUNC.CODSECAO
				  ,	PSECAO.DESCRICAO
				  ,	CAST(AMOVFUN.NUMHORAS AS VARCHAR)  AS HORAS
				  ,	AMOVFUN.CODEVE
				  ,	AMOVFUN.INICIOPER
				  ,	AMOVFUN.FIMPER
				  ,	AMOVFUN.ANOCOMP
				  ,	AMOVFUN.MESCOMP

	  FROM AMOVFUN 
		   INNER JOIN PFUNC ON PFUNC.CODCOLIGADA = AMOVFUN.CODCOLIGADA AND PFUNC.CHAPA = AMOVFUN.CHAPA
		   INNER JOIN PSECAO ON PSECAO.CODCOLIGADA = PFUNC.CODCOLIGADA AND PSECAO.CODIGO = PFUNC.CODSECAO
   
	  WHERE AMOVFUN.CODCOLIGADA = @CODCOLIGADA_I
		AND AMOVFUN.ANOCOMP = @ANOCOMP_I 
		AND AMOVFUN.MESCOMP = @MESCOMP_I 
		AND PFUNC.CODSITUACAO <> 'D'

   ) AS TABELA
  
   PIVOT (MAX(HORAS) FOR CODEVE IN ((SELECT item FROM dbo.SplitStrings((dbo.fn_COLUNAS_PIVOT(@CODCOLIGADA_I, @ANOCOMP_I, @MESCOMP_I)), ','))) COLUNAS