CODEWITHSUNDEEP
sqlsql-server
Chanchan
Chanchan

Reputation: 89

Shuffle column values per row

I'm developing a Multiple-Choice Exam Generator using C#.NET. Every time a report is made, questions are picked randomly in the database and choices are shuffled randomly. I can do the random questions part, but i can't do the shuffling of choices.

I have a table with a row like this:

-----------------------------------------------------
|question|answer|distractor1|distractor2|distractor3|
|q1      |ansq1 |d1q1       |d2q1       |d2q1       |
-----------------------------------------------------

What I would like to happen is to shuffle the values of column answer, distractor1, and distractor3. For example:

-----------------------------------------------------
|question|answer|distractor1|distractor2|distractor3|
|q1      |d3q1  |d2q1        |d1q1       |ansq1     |
-----------------------------------------------------

is this possible using SQL queries?

Upvotes: 3

Views: 396

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Here is one trick using Dynamic Sql

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

Upvotes: 1

Related Questions