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

Dynamic Crosstab transforming MONEY data from columns to rows

/*
Dynamic Crosstab transforming MONEY 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
*/

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 MONEY
             )

INSERT INTO #Aggregates
            (
             RowText,
             ColumnText,
             CellData
            )

------------------------------------
-- source query to be transformed --
------------------------------------
/*
i have picked an adventureworks table
as an example.
*/

select
  LastName + ' , ' + FirstName,
  row_number() over (partition by eph.EmployeeID order by LastName + ' , ' + FirstName),
  Rate
from
  HumanResources.EmployeePayHistory eph
inner join
  HumanResources.Employee e
  on e.EmployeeID = eph.EmployeeID
inner join
  Person.Contact c
  on e.ContactID = c.ContactID

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

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) + ' INT NULL DEFAULT 0'
      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