Rearrange the Answers

alter
Procedure GetRandomOptions

as

Begin

Create
Table #Options(ID int identity(1,1),Fields nvarchar(10))
Insert
into #Options

Select
COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME='Questions' And COLUMN_NAME like 'O%' Order By NEWID()

--Select * from #Options

Declare
@RandomCols nvarchar(100)

Set
@RandomCols=''

Select
@RandomCols=@RandomCols+Fields+' ,' from #Options

--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O1,')

+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))

--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O2,')

+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))

--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O3,')

+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))

--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O4,')

+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))

--Set @RandomCols=SUBSTRING(@RandomCols,0,len(@RandomCols))

--Select @RandomCols

Declare
@strQry nvarchar(MAX)

Set
@strQry=''

Set
@strQry='Select QID,Ques,'+@RandomCols+'Ans from Questions '
exec
(@strQry)

End