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