ALTER  PROC [dbo].[spzzzRotate_Table]
/* enero 2009 cambie la funcion SUM por AVG para que si tiene 2 resultados 
el mismo día grafique el promedio */

	@strSQLOut	 varchar(8000)  = NULL OUTPUT,
	@onTable     sysname,        		
	@onrows      nvarchar(128),		
	@onrowsalias sysname = NULL,		
	@oncols      nvarchar(128),		
	@sumcol	 sysname = NULL,		
	@onWhereKeys varchar(500) = NULL,	
	@onWhere	 varchar(500) = NULL,
	@onCaseWhere varchar(500) = NULL,	
	@onInsertTable sysname = NULL		
AS
BEGIN
	SET NOCOUNt ON

	DECLARE @strSQL varchar(8000),
			@NEWLINE varchar(1)

	SET @NEWLINE = ' '

	--Paso 2: Inicializar la instrucción Select
	SET @strSQL =
			  'SELECT'	+ @NEWLINE + 
			  			+ @onrows +
			  CASE
			    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
			    ELSE ''
			  END

	--Paso 3: Leer los valores pivote
	CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)
	
	DECLARE @keyssql AS varchar(1000)
	SET @keyssql = 
				'INSERT INTO #keys ' +
				'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100))' + @NEWLINE +
				'FROM ' + @onTable + @NEWLINE +
				CASE
					WHEN NOT @onWhereKeys IS NULL THEN 'WHERE ' + @onWhereKeys
					ELSE ''
				END
	
	EXEC (@keyssql)
	
	--Paso 4: Construir la instrucción select
	DECLARE @strSQL1 varchar(8000)
	DECLARE @key AS nvarchar(100)
	SELECT @key = MIN(keyvalue) FROM #keys

	WHILE @key IS NOT NULL
	BEGIN
		SET @strSQL = @strSQL + CAST(',' + @NEWLINE +
							' AVG(CASE WHEN CAST(' + @oncols +
					                 ' AS Nvarchar(100)) = ' + @NEWLINE +
							' N''' + @key + '''' + @NEWLINE +
							CASE 
								WHEN NOT @onCaseWhere IS NULL THEN @onCaseWhere + @NEWLINE
								ELSE ''
							END +
					       	' THEN ' + CASE
					                      WHEN @sumcol IS NULL THEN '1'
					                      ELSE @sumcol
					                    END + @NEWLINE +
							' ELSE 0'                      + @NEWLINE +
							' END) AS ''' + @key + '''' as varchar(1000))
	  
		SELECT	@key = MIN(keyvalue) 
		FROM	#keys
		WHERE	keyvalue > @key

		IF len(@strSQL) > 7000
		BEGIN
			SET @strSQL1 = @strSQL
			SET @strSQL = ''
		END
	END

	--Paso 5: Finalizar la instruccion Select
	SET @strSQL =	@strSQL 	+ CAST(	@NEWLINE +
								'FROM ' + @onTable + @NEWLINE +
								CASE
									WHEN @onWhere IS NULL THEN ''
									ELSE 'WHERE ' + @onWhere + @NEWLINE
								END +
								'GROUP BY ' + @onrows + @NEWLINE +
								'ORDER BY ' + @onrows as varchar(1000))

	--Paso 6: Ejecución y retorno
	EXEC (@strSQL1 + @strSQL)
	SET @strSQLOut	= @strSQL

	--Paso 7: Inserción de datos
	IF NOT @onInsertTable IS NULL
	BEGIN
		--INSERTAR LOS DATOS EN UNA TABLA TEMPORAL O LOCAL
		DECLARE @InsertSQL varchar(8000)

		IF (CHARINDEX('#',@onInsertTable) = 0)
			 BEGIN
				SET @strSQL1 = CAST('INSERT INTO ' + @onInsertTable + @NEWLINE AS VARCHAR(8000)) + 
								cASE
									WHEN LEN(@strSQL1) > 0 THEN @strSQL1
									ELSE @strSQL
								END
			 END
			ELSE
			 BEGIN	
				SET @InsertSQL = SUBSTRING(@strSQL, 1, CHARINDEX('FROM', @strSQL) - 1)
		
				SET @InsertSQL = @InsertSQL + CAST(@NEWLINE + 
								'INTO ' + @onInsertTable + @NEWLINE +
								SUBSTRING(@strSQL, CHARINDEX('FROM', @strSQL), LEN(@strSQL)) AS VARCHAR(8000))
			 END

		EXEC (@strSQL1 + @InsertSQL)
	END

	--ELIMINAR LOS DATOS TEMPORALES
	DROP TABLE #keys
END