Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Table with a "Question" column and 5 "answer" col.

Author  Topic 

Prufrock42
Starting Member

1 Post

Posted - 2014-10-30 : 13:25:42
It feels like this should be very simple and I'm overlooking something completely obvious.

I have a table with 6 columns: Question, CorrectAnswer, Incorrect1, Incorrect2, Incorrect3, Incorrect4.

I want to write a select statement that pulls all 6 columns from the table, but displays the answer columns in random order (or even alphabetical order would be fine).

Basically, I'm creating a little webform for students to take a short quiz. But I don't want the "CorrectAnswer" field to display in the same column each time, as that would make it obvious for the students.

Any suggestions would be much appreciated!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-31 : 08:44:34
Are you comfortable with dynamic SQL? If so:

1. build a list of the six columns in a random order
2. build a sql query using the list
3. execute the query

e.g.


CREATE TABLE #t(a int, b int, c int)

-- build a list of columns in random order

DECLARE @collist NVARCHAR(4000) = stuff(
(
SELECT ',' + quotename(nm)
FROM (VALUES ('a'),('b'),('c')) v(nm)
ORDER BY NEWID()
FOR XML PATH('')
)
, 1,1,'')

-- build the query

DECLARE @sql nvarchar(4000) =
'select '
+ @collist
+ 'from #t'

-- execute the query

EXEC (@sql)
Go to Top of Page
   

- Advertisement -