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
		   AND COLUMN_NAME<>'ExamID'
			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


 
Advertisements


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s