Pivot Table Recordset for SQL 2000

-- Create test data
CREATE TABLE#SOURCETABLE
(
COLUMNFIELD VARCHAR(20),
ROWFIELD VARCHAR(20),
DATAFIELD INT
)

INSERT INTO #SOURCETABLE VALUES ('C1', 'R1', 12)
INSERT INTO #SOURCETABLE VALUES ('C1', 'R2', 1)
INSERT INTO #SOURCETABLE VALUES ('C2', 'R1', -5)
INSERT INTO #SOURCETABLE VALUES ('C2', 'R2', 10)
INSERT INTO #SOURCETABLE VALUES ('C3', 'R1', 7)
INSERT INTO #SOURCETABLE VALUES ('C3', 'R2', 0)

-- Here is the real code
CREATE TABLE#Headers
(
HeaderIndex INT IDENTITY (0, 1),
HeaderCaption VARCHAR(20)
)

INSERT INTO#Headers
(
HeaderCaption
)
SELECT DISTINCT#SOURCETABLE.COLUMNFIELD
FROM#SOURCETABLE
ORDER BY#SOURCETABLE.COLUMNFIELD

CREATE TABLE#Rows
(
RowCaption VARCHAR(20)--SELECT ANOTHER FIELDNAME FOR NICER OUTPUT
)

INSERT INTO#Rows
(
RowCaption
)
SELECT DISTINCT#SOURCETABLE.ROWFIELD
FROM#SOURCETABLE

DECLARE@HeaderIndex INT,
@MaxHeaderIndex INT,
@HeaderCaption VARCHAR(20),
@SQL VARCHAR(1000)

SELECT@HeaderIndex = 0,
@MaxHeaderIndex = MAX(#Headers.HeaderIndex)
FROM#Headers

WHILE @HeaderIndex <= @MaxHeaderIndex
BEGIN
SELECT@HeaderCaption = #Headers.HeaderCaption
FROM#Headers
WHERE#Headers.HeaderIndex = @HeaderIndex

SELECT@SQL = 'ALTER TABLE #Rows ADD [' + @HeaderCaption + '] INT NULL'
EXEC(@SQL)

SET@SQL = 'UPDATE #Rows SET #Rows.[' + @HeaderCaption + '] = (SELECT SUM(#SOURCETABLE.DATAFIELD) FROM #SOURCETABLE WHERE #SOURCETABLE.COLUMNFIELD = ''' + @HeaderCaption + ''' AND #Rows.RowCaption = #SOURCETABLE.ROWFIELD)'
EXEC(@SQL)

SET @HeaderIndex = @HeaderIndex + 1
END

SELECT*
FROM#Rows
ORDER BY#Rows.RowCaption

DROP TABLE#Headers
DROP TABLE#Rows


-- Drop the test data
DROP TABLE #SOURCETABLE