Arrange Column Order In a Select Statement (Dynamic query)

--create table test (examID int, col1 int, col4 int, col3 int, col2 int)
--Insert into test values (1, 1,2,3,4),(1,4,3,2,1),(1,9,9,9,9)

Declare @ExamID int=1

DECLARE @colsSorted AS NVARCHAR(2000),
   @sql  AS NVARCHAR(4000)

   declare @mytable varchar(50)='test', @myschema  varchar(50) ='dbo'

select @colsSorted 
  = STUFF((select ', '+quotename(column_name,'[')
           from information_schema.columns 
           where table_name = @mytable and TABLE_SCHEMA=@myschema
			Order by NewID()
           FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')

print  @colsSorted

Set @sql=N'SELECT '+ @colsSorted  +  ' FROM ' + @myschema+ '.'+ @mytable + ' WHERE ExamID='+ CAST(@ExamID as varchar(4))

exec sp_executesql @sql;

---    drop table test


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s