如何实现SQL表中每行指定列值的独立随机打乱？

The problem with the original query you shared is that it selects a single set of column names once and applies that same shuffle to every row in the table. That's why all rows end up with identical orderings for answer, distractor1, etc. To get a unique shuffle per row, we need to handle each row's values independently.

Solution: Use CROSS APPLY + Random Row Numbering #

This approach unpivots your option columns into rows, assigns a random order per question/row, then pivots them back into columns. Here's how to do it:

Step 1: Query Shuffled Results (No Update)

If you just want to view the shuffled data without modifying the original table:

WITH ShuffledOptions AS (
    SELECT
        t.question,
        opt.option_value,
        -- Generate random row numbers *per row/question*
        ROW_NUMBER() OVER (PARTITION BY t.question ORDER BY NEWID()) AS rn
    FROM yourtable t
    CROSS APPLY (
        -- Replace these with your actual option columns (exclude `question`)
        VALUES 
            (t.ansq1), -- Your original answer column
            (t.d1q1),  -- First distractor column
            (t.d2q1),  -- Second distractor column
            (t.d3q1)   -- Third distractor column
        ) opt(option_value)
)
SELECT
    question,
    MAX(CASE WHEN rn = 1 THEN option_value END) AS answer,
    MAX(CASE WHEN rn = 2 THEN option_value END) AS distractor1,
    MAX(CASE WHEN rn = 3 THEN option_value END) AS distractor2,
    MAX(CASE WHEN rn = 4 THEN option_value END) AS distractor3
FROM ShuffledOptions
GROUP BY question
ORDER BY question;

Step 2: Update the Original Table with Shuffled Values

If you need to permanently update the table with the shuffled values, use a temporary table to store the results first:

-- Create temp table to hold shuffled values per row
SELECT
    question,
    MAX(CASE WHEN rn = 1 THEN option_value END) AS shuffled_answer,
    MAX(CASE WHEN rn = 2 THEN option_value END) AS shuffled_distractor1,
    MAX(CASE WHEN rn = 3 THEN option_value END) AS shuffled_distractor2,
    MAX(CASE WHEN rn = 4 THEN option_value END) AS shuffled_distractor3
INTO #ShuffledTemp
FROM (
    SELECT
        t.question,
        opt.option_value,
        ROW_NUMBER() OVER (PARTITION BY t.question ORDER BY NEWID()) AS rn
    FROM yourtable t
    CROSS APPLY (
        -- Again, replace with your actual option columns
        VALUES 
            (t.ansq1),
            (t.d1q1),
            (t.d2q1),
            (t.d3q1)
    ) opt(option_value)
) AS subquery
GROUP BY question;

-- Update original table with shuffled data
UPDATE yourtable
SET
    answer = st.shuffled_answer,
    distractor1 = st.shuffled_distractor1,
    distractor2 = st.shuffled_distractor2,
    distractor3 = st.shuffled_distractor3
FROM yourtable t
JOIN #ShuffledTemp st ON t.question = st.question;

-- Clean up temporary table
DROP TABLE #ShuffledTemp;

-- Check the updated results
SELECT * FROM yourtable;

Why This Works #

• CROSS APPLY takes each row's option values and turns them into individual rows, so we can process each value separately.
• ROW_NUMBER() OVER (PARTITION BY t.question ORDER BY NEWID()) ensures that for every question (row), we get a unique random order of the options. The PARTITION BY clause keeps the randomization isolated to each row.
• The final GROUP BY and CASE statements pivot the randomized rows back into columns, giving each row its own unique shuffle.

Why the Original Query Failed #

The original WHILE loop only runs once: it picks one set of column names (e.g., @answer = d3q1, @distractor1 = d2q1) and applies that same mapping to every row in the table. That's why all rows ended up with identical column orderings.

内容的提问来源于stack exchange，提问作者Chanchan