BEGIN

DECLARE @MAX INT
DECLARE @CREATE_TABLE NVARCHAR(MAX)

--- Query to determine the amount of columns needed for horizontal table.
SELECT @MAX = MAX(cnts)
FROM (
SELECT ManagerName, ManagerEmail, COUNT(*) As cnts
FROM dbo.VirticalTable
GROUP BY managerName, ManagerEmail ) inline
PRINT @MAX

-- Dynamically create table that will hold virtical columns
DECLARE @I INT
DECLARE @STR VARCHAR(MAX)
SET @I = 1
SET @STR = ''
WHILE (@I <= @MAX) BEGIN
	SET @STR = @STR + 'Review'+ CAST(@I AS VARCHAR(10))+ ' NVARCHAR(MAX), EmployeeName'+ CAST(@I AS VARCHAR(10))+' NVARCHAR(MAX), ' 
	SET @I = @I + 1
END
SELECT @CREATE_TABLE = 'CREATE TABLE dbo.HorzTable ('+@STR+ ' EvaluatorEmail NVARCHAR(255), EvaluatorName NVARCHAR(255))' 
PRINT @CREATE_TABLE
EXECUTE sp_executesql @CREATE_TABLE


-- Loop virtical table to crate insert statements for the horizontal table
DECLARE @ManagerEmail NVARCHAR(255)
DECLARE @ManagerName NVARCHAR(255)
DECLARE @Status		NVARCHAR(255)
DECLARE @Cnts INT
DECLARE @InsertString NVARCHAR(MAX)
DECLARE PersonCursor CURSOR FAST_FORWARD FOR
	SELECT ManagerName, ManagerEmail, COUNT(*) As cnts
	FROM dbo.VirticalTable
	GROUP BY managerName, ManagerEmail 
OPEN PersonCursor
	FETCH NEXT FROM PersonCursor
	INTO @ManagerName, @ManagerEmail, @Cnts
WHILE @@FETCH_STATUS = 0 BEGIN
      -- Remove extra chars and make ' be ''.  This is needed incase you occur something like O'Connell ...
      DECLARE @EvalEmail NVARCHAR(MAX)
      DECLARE @EvalName NVARCHAR(MAX)
      
      SET @EvalEmail = RTRIM(LTRIM(REPLACE(@ManagerEmail,'''','')))
      SET @EvalName = RTRIM(LTRIM(REPLACE(@ManagerName,'''','')))
     
      -- Start the creation of the insert statement 
      SELECT @InsertString = 'INSERT INTO dbo.HorzTable ('
      DECLARE @x INT
      SET @x = 1
      WHILE @x <= @Cnts BEGIN
			SET @InsertString = @InsertString + 'Review'+ CAST(@x AS VARCHAR(10))+ ', EmployeeName'+ CAST(@x AS VARCHAR(10))+ ',' 		
			SET @x = @x + 1
      END
      SELECT @InsertString = @InsertString + 'EvaluatorEmail, EvaluatorName) VALUES ('
      --- Add values claus to the insert statement
      	 DECLARE @strVal nvarchar(max)
		 SET @strVal = ''
		 SELECT @strVal = + @strVal +',' + ''''+review+''''+','+''''+EmployeeName+''''
		 FROM (
			SELECT RTRIM(LTRIM(review)) AS review, RTRIM(LTRIM(REPLACE(EmployeeName,'''','''''') )) AS EmployeeName 
			FROM dbo.virticalTable
			WHERE ManagerEmail like @ManagerEmail and ManagerName like @ManagerName ) T1
		 SELECT @strVal = SUBSTRING(@strVal,2,len(@strVal) - 1)
		 SELECT @InsertString = @InsertString + @strVal
		 SELECT @InsertString = @InsertString +','+''''+@EvalEmail+''''+ ',' + ''''+@EvalName+''''+')'
			  PRINT @InsertString
			  EXECUTE sp_executesql @InsertString
		      
			  FETCH NEXT FROM PersonCursor INTO @ManagerName, @ManagerEmail, @Cnts
		END
CLOSE PersonCursor
DEALLOCATE PersonCursor

SELECT * FROM dbo.HorzTable
DROP TABLE dbo.HorzTable
END

--DROP TABLE dbo.HorzTable