Table with a "Question" column and 5 "answer" col.

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources