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.
--Jeff Moden
Change is inevitable... Change for the better is not.