Tuesday, February 20, 2007

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
 

No comments: