Thanks... I appreciate that. Here's the solution... explanation is in the code comments.

--===== This is your original test table with just a bit of reformatting.

-- I'd recommend that you change DECIMAL(15,0) to INT or BIGINT.

CREATE TABLE #Test_Table

(

Test_key DECIMAL(15,0),

Question_Key DECIMAL(15,0),

Result VARCHAR(50)

)

INSERT INTO #Test_Table

(Test_Key, Question_Key, Result)

SELECT 12345, 567, 'abc' UNION ALL

SELECT 12345, 234, 'xyz' UNION ALL

SELECT 12345, 567, 'def' UNION ALL

SELECT 12345, 567, 'ghi' UNION ALL

SELECT 12345, 234, 'wxy' UNION ALL

SELECT 12345, 123, 'lmn' UNION ALL

SELECT 12345, 789, 'tuv' UNION ALL

SELECT 12345, 123, 'lmn' UNION ALL

SELECT 12345, 567, 'efg'

GO

--===== Assuming that the original table cannot be changed,

-- create and populate a new table on the fly. The

-- IDENTITY column will reflect the desired sort order

-- for creating a "Sequence" column. ROW_NUMBER() or

-- "RANK" in SQL Server 2005 would make this a lot easier.

SELECT IDENTITY(INT,1,1) AS RowNum,

Test_Key,

Question_Key,

CAST(0 AS INT) AS Sequence,

Result

INTO #MyWork

FROM #Test_Table

ORDER BY Test_Key, Question_Key, Result DESC

--===== Adding this clustered index is critical to getting

-- the ensuing UPDATE to do things in the correct order.

-- It MUST be the clustered index to work correctly.

ALTER TABLE #MyWork

ADD PRIMARY KEY CLUSTERED (RowNum)

WITH FILLFACTOR = 100

--===== Declare a couple of obvious named variables...

DECLARE @Prev_Test_Key INT,

@Prev_Question_Key INT,

@Prev_Sequence INT

--===== Do the proprietary 3 part update.

-- This works just as if you did it in a loop because,

-- behind the scenes, and UPDATE IS a loop except it's

-- ten's of times faster than a declared loop.

UPDATE #MyWork

SET @Prev_Sequence = Sequence = CASE

WHEN @Prev_Test_Key = Test_Key

AND @Prev_Question_Key = Question_Key

THEN @Prev_Sequence + 1

ELSE 1

END,

@Prev_Test_Key = Test_Key,

@Prev_Question_Key = Question_Key

FROM #MyWork WITH (TABLOCKX)

OPTION (MAXDOP 1)

--===== Run the cross tab query to use the new Sequence column

SELECT Test_Key,

MAX(CASE WHEN Question_Key = 567 THEN Result END) AS [First],

MAX(CASE WHEN Question_Key = 234 THEN Result END) AS [Second],

MAX(CASE WHEN Question_Key = 123 THEN Result END) AS [Third],

MAX(CASE WHEN Question_Key = 789 THEN Result END) AS [Fourth]

FROM #MyWork

GROUP BY Test_Key, Sequence

If the original table is a partitioned table, this will still work because of the intermediate #MyWork table we created.