`PIVOT (funcionDeAgregado(campoDeAgregado)FOR ColumnaPivote IN (valoresDeColumnaPivote)) AS Alias;`

USE [tuEsquema]
GO

/****** Object:  StoredProcedure [dbo].[prcPivotDinamic]    Script Date: 6/21/2018 2:32:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[prcPivotDinamic]
    -- Add the parameters for the stored procedure here
    @fchIni VARCHAR(255),
    @fchFin VARCHAR(255),
    @identificacion VARCHAR(255)
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @periodos AS NVARCHAR(MAX)
    DECLARE @PivotDynamicEsp NVARCHAR(MAX)
    DECLARE @PivotDynamicGnral NVARCHAR(MAX)
    DECLARE @dropViewGnral NVARCHAR(MAX)
    DECLARE @dropViewEsp NVARCHAR(MAX)

    SELECT @periodos = COALESCE(@periodos + ', ', '')+ Quotename(Fecha)
                        FROM
                        (
                            SELECT DISTINCT Fecha
                            FROM consultHistoricoEstGnral
                            WHERE Fecha BETWEEN @fchIni AND @fchFin
                            GROUP BY Fecha
                        ) AS periodos

        --print 'periodos: '+@periodos;

         SET @dropViewGnral = 'DROP VIEW MyPivotDynamicGnral'+@identificacion

         --print @dropViewGnral

            if object_id('MyPivotDynamicGnral'+@identificacion,'v') is not null
                EXEC sp_executesql @dropViewGnral

        SET @dropViewEsp = 'DROP VIEW MyPivotDynamicEsp'+@identificacion

            if object_id('MyPivotDynamicEsp'+@identificacion,'v') is not null
                EXEC sp_executesql @dropViewEsp

            SET @PivotDynamicEsp = ' CREATE VIEW MyPivotDynamicEsp'+@identificacion+' AS
            SELECT
                EstadoEspecifico,
                '+@periodos+'
            FROM
                (
                    SELECT
                        CantidadEspecifica,
                        EstadoEspecifico,
                        Fecha
                    FROM consultHistoricoEstEsp
                ) AS SourceTable
            PIVOT
                (
                SUM(CantidadEspecifica)
                FOR Fecha IN ('+@periodos+')
                ) AS PivotTable;
            '
            SET @PivotDynamicGnral = 'CREATE VIEW MyPivotDynamicGnral'+@identificacion+' AS
            SELECT
                EstadoGeneral,
                '+@periodos+'
            FROM
                (
                    SELECT
                        CantidadGeneral,
                        EstadoGeneral,
                        Fecha
                    FROM consultHistoricoEstGnral
                ) AS SourceTable
            PIVOT
                (
                SUM(CantidadGeneral)
                FOR Fecha IN ('+@periodos+')
                ) AS PivotTable;'

            --print @PivotDynamicGnral

            IF(@periodos <> '')
                BEGIN
                    EXEC sp_executesql @PivotDynamicEsp
                    EXEC sp_executesql @PivotDynamicGnral
                    print 'done'
                END
            ELSE
                PRINT 'notRow'


END
GO