SQL - Pivot Dinamico

DECLARE @sprdElements AS NVARCHAR(MAX) --comma separated, delimited, distinct list of product attributes

        ,@tSql AS NVARCHAR(MAX)        --query text

        ,@CodViajeros VARCHAR(255);     --specific product name

-- SET @CodViajeros = NULL -- por si queremos filtrar

;WITH dsitSpreadElList AS

(

    SELECT distinct Vehiculo,Hora,CodigoLinea,Sentido,Conductor,FechaPrevista,CodViajeros

    FROM #VIA_CRUCE

)

SELECT @sprdElements = COALESCE(@sprdElements+', ','')+'['+ CAST( CodViajeros AS NVARCHAR(255))+']'

FROM dsitSpreadElList;

SET @tSql =N';WITH TabExp AS

             (

                SELECT Vehiculo	-- grouping element

					  ,Hora

					  ,CodigoLinea

					  ,Sentido

					  ,Conductor

					  ,FechaPrevista

                      ,CodViajeros		-- spreading element

                      ,[TotalViajeros]		-- aggregating element

                FROM #VIA_CRUCE

	     )

             SELECT Vehiculo,Hora,CodigoLinea,Sentido,Conductor,FechaPrevista,'+@sprdElements +N'

             FROM TabExp

             PIVOT (

                    sum([TotalViajeros])

                    FOR CodViajeros IN (' + @sprdElements +N')

                    ) AS pvt';

 EXEC sys.sp_executesql

     @stmt = @tSql