Shuffle column values of multiple rows

Question

I posted a similar question here: Shuffle column values per row

The query given by Pரதீப் is this:

WHILE 1 = 1
    BEGIN
        DECLARE @answer SYSNAME = (SELECT TOP 1 COLUMN_NAME
            FROM   INFORMATION_SCHEMA.columns
            WHERE  TABLE_NAME = 'yourtable' AND COLUMN_NAME NOT IN ( 'question', 'answer')
            ORDER  BY Newid())
        DECLARE @distractor1 SYSNAME = (SELECT TOP 1 COLUMN_NAME
            FROM   INFORMATION_SCHEMA.columns
            WHERE  TABLE_NAME = 'yourtable' AND COLUMN_NAME NOT IN ( 'question', @answer, 'distractor1' )
            ORDER  BY Newid())
        DECLARE @distractor2 SYSNAME = (SELECT TOP 1 COLUMN_NAME
            FROM   INFORMATION_SCHEMA.columns
            WHERE  TABLE_NAME = 'yourtable' AND COLUMN_NAME NOT IN ( 'question', @answer, 'distractor2', @distractor1 )
            ORDER  BY Newid())
        DECLARE @distractor3 SYSNAME = (SELECT TOP 1 COLUMN_NAME
            FROM   INFORMATION_SCHEMA.columns
            WHERE  TABLE_NAME = 'yourtable' AND COLUMN_NAME NOT IN ( 'question', @answer, 'distractor3', @distractor2, @distractor1 )
            ORDER  BY Newid())

        IF @distractor1 IS NOT NULL
            AND @distractor2 IS NOT NULL
            AND @distractor3 IS NOT NULL
            AND @answer IS NOT NULL
        BREAK
    END
--select @distractor1,@distractor2,@distractor3
exec( 'update yourtable set answer = '+@answer+', distractor1 = '+@distractor1+', distractor2 = '+@distractor2+', distractor3 = '+@distractor3)

select * from yourtable

It works. However, it looks like this: https://prnt.sc/hwraqa

There's a pattern emerging from the query. Whatever order the first row had, the next rows will also have that same order. What I would like to happen is something like this:

-----------------------------------------------------
|question|answer|distractor1|distractor2|distractor3|
|q1      |d3q1  |d2q1       |d1q1       |ansq1      |
|q2      |d1q2  |d3q3       |ansq2      |d2q2       |
|q3      |ansq3 |d1q3       |d3q3       |d2q3       |
-----------------------------------------------------

Each row should have a unique order and is shuffled.

Answer 1

Here's one way of doing it. Demo.

It is similar to podiluska's answer but simpler as it only applies to the randomisation, unpivoting and pivoting to values in the current row.

UPDATE YourTable
SET    answer = CA.[1],
       distractor1 = CA.[2],
       distractor2 = CA.[3],
       distractor3 = CA.[4]
FROM   YourTable
       CROSS APPLY (SELECT *
                    FROM   (SELECT x,
                                   ROW_NUMBER() OVER (ORDER BY CRYPT_GEN_RANDOM(4)) AS rn
                            FROM   (VALUES(answer),
                                          (distractor1),
                                          (distractor2),
                                          (distractor3)) V(x)) ps 
                            PIVOT (MAX(x) FOR rn IN ([1], [2], [3], [4])) p) CA 

NB: I assumed you are looking for an UPDATE statement based on the previous question you linked. If you need a SELECT just replace UPDATE YourTable SET with SELECT question, - demo

Answer 2

Your question is little bit broad and hard to answer. And answer to it is little bit long, but here it is:

1) preparation of tables - first you have already, which is source table, but we need also destination table (after all you can clear old table and fill with values from new one):

create table #Q_and_A(
  question char(2),
  answer varchar(5),
  distractor1  varchar(5),
  distractor2  varchar(5),
  distractor3  varchar(5)
)
create table #Q_and_A_shuffled(
  question char(2),
  answer varchar(5),
  distractor1  varchar(5),
  distractor2  varchar(5),
  distractor3  varchar(5)
)
insert into #Q_and_A values
('q1','ansq1', 'd1q1', 'd2q1', 'd3q1'),
('q2','ansq2', 'd1q2', 'd2q2', 'd3q2'),
('q3','ansq3', 'd1q3', 'd2q3', 'd3q3')

2) Having that, we can start with some procedure to shuffle as you want. In order to do that, we need to have all combinations of names of your columns, so we do such thing:

declare @ColumnCombination table (col varchar(20))
insert into @ColumnCombination values ('answer'),('distractor1'),('distractor2'),('distractor3')  

Now, query:

select top 1 @insertQuery = 'insert into #Q_and_A_shuffled select question,' + Combinations + ' from #Q_and_A where question = ''' + @qst + '''' from (
    select CC1.col + ',' + CC2.col + ',' + CC3.col + ',' + CC4.col Combinations from @ColumnCombination CC1
    cross join @ColumnCombination CC2 cross join @ColumnCombination CC3 cross join @ColumnCombination CC4
    where CC1.col <> CC2.col and CC1.col <> CC3.col and CC1.col <> CC4.col and
          CC2.col <> CC3.col and CC2.col <> CC4.col and CC3.col <> CC4.col
) as a order by newid()

will return single SQL insert statement, which will insert shuffled rows for particular question (variable @qst) to new table.

Putting all these considerations together, we obtain:

declare @cnt int, @i int, @qst char(2), @insertQuery nvarchar(1000)
set @i = 1
select @cnt = count(*) from #Q_and_A
declare @ColumnCombination table (col varchar(20))
insert into @ColumnCombination values ('answer'),('distractor1'),('distractor2'),('distractor3')

while @i <= @cnt
begin
    set @qst = 'q' + cast(@i as varchar(2))
    select top 1 @insertQuery = 'insert into #Q_and_A_shuffled select question,' + Combinations + ' from #Q_and_A where question = ''' + @qst + '''' from (
        select CC1.col + ',' + CC2.col + ',' + CC3.col + ',' + CC4.col Combinations from @ColumnCombination CC1
        cross join @ColumnCombination CC2 cross join @ColumnCombination CC3 cross join @ColumnCombination CC4
        where CC1.col <> CC2.col and CC1.col <> CC3.col and CC1.col <> CC4.col and
              CC2.col <> CC3.col and CC2.col <> CC4.col and CC3.col <> CC4.col
    ) as a order by newid()

    exec sp_executesql @insertQuery
    set @i = @i + 1
end

select * from #Q_and_A
select * from #Q_and_A_shuffled

Example result:

Answer 3

The root of your problem is a denormalised data structure. You should store the answers in a separate table to the questions, which makes it easier to randomise the order of the answers.

-- sample data
declare @questions table (
    question varchar(10), 
    answer varchar(10),     
    distractor1 varchar(10), 
    distractor2 varchar(10), 
    distractor3 varchar(10)
)

insert @questions
select 'q1','a1','d11','d12','d13'
union all select 'q2','a2','d21','d22','d23'
union all select 'q3','a3','d31','d32','d33'

select results.*, q.answer
from
(
select question, response, row_number() over (partition by question     order by newid()) rn
from
(
    select question, answerType, response
    from @questions
    unpivot (answerType for response in (distractor1, distractor2, distractor3)) u
    union all
    select question, 'answer', answer as answerorder from @questions
) normalised -- the answers in a normalised form
) randomised  -- randomise the order
pivot (max(response) for rn in ([1],[2],[3],[4])) results
inner join @questions q on results.question = q.question