Dynamic Crosstab transforming TEXT data from columns to rows

/*
Dynamic Crosstab transforming TEXT data from columns to rows

based on sql server central example, but modified to
1) remove error on table index hints
2) drop temporary objects first
3) perform transformation on text data (the original uses MONEY data type)
*/

Use AdventureWorks
go

if object_id('tempdb..#Aggregates') is not null
 begin
 drop table #Aggregates
 end
if object_id('tempdb..#Columns') is not null
 begin
 drop table #Columns
 end
if object_id('tempdb..#Rows') is not null
 begin
 drop table #Rows
 end

CREATE TABLE #Aggregates
             (
              RowText VARCHAR(50),
              ColumnText VARCHAR(50),
              CellData VARCHAR(50)
             )

INSERT INTO #Aggregates
            (
             RowText,
             ColumnText,
             CellData
            )


------------------------------------
-- source query to be transformed --
------------------------------------
/*
i have picked an adventureworks table
as an example.
*/
select 
  GroupName,
  row_number() over (partition by GroupName order by Name),
  Name
from 
  HumanResources.Department

------------------------------------


CREATE UNIQUE INDEX IX_Aggregates ON #Aggregates (RowText, ColumnText, CellData)
 
CREATE TABLE #Columns
            (
             ColumnIndex INT IDENTITY (0, 1),
             ColumnText VARCHAR(50)
             )
 
INSERT INTO     #Columns
                (
                 ColumnText
                )
SELECT DISTINCT ColumnText
FROM           #Aggregates WITH (INDEX(IX_Aggregates), NOLOCK)
ORDER BY       ColumnText
 
CREATE UNIQUE INDEX IX_Columns ON #Columns (ColumnIndex, ColumnText)
 
CREATE TABLE #Rows
             (
              RowText VARCHAR(50)
             )
 
INSERT INTO    #Rows
                (
                 RowText
                )

SELECT distinct RowText
FROM            #Aggregates WITH (INDEX(IX_Aggregates), NOLOCK)
 
CREATE UNIQUE INDEX IX_Rows ON #Rows (RowText)
 
DECLARE     @ColumnIndex INT,
            @MaxColumnIndex INT,
            @ColumnText VARCHAR(50),
            @SQL VARCHAR(1000)
 
SELECT      @ColumnIndex = 0,
            @MaxColumnIndex = MAX(ColumnIndex)
FROM        #Columns


WHILE @ColumnIndex <= @MaxColumnIndex
   BEGIN
      SELECT     @ColumnText = ColumnText
      FROM       #Columns
      WHERE      ColumnIndex = @ColumnIndex
 
      SELECT     @SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' VARCHAR(50) NOT NULL DEFAULT '''''
      EXEC       (@SQL)
 
      SELECT     @SQL = 'UPDATE #Rows SET ' + QUOTENAME(@ColumnText) + ' = #Aggregates.CellData
                         FROM #Aggregates WITH (INDEX(IX_Aggregates), NOLOCK)
            , #Columns WITH (INDEX(IX_Columns), NOLOCK)
         WHERE #Rows.RowText = #Aggregates.RowText
         AND #Columns.ColumnText = #Aggregates.ColumnText
         AND #Columns.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
      EXEC       (@SQL)
 
      SELECT  @ColumnIndex = @ColumnIndex + 1
   END

DROP TABLE #Columns
DROP TABLE #Aggregates

SELECT     #Rows.*
FROM        #Rows
ORDER BY   #Rows.RowText

DROP TABLE  #Rows