Is it possible to select sql server data using column ordinal position

Solution 1:

If you know quantity of columns, but don't know its names and types, you can use following trick:

select NULL as C1, NULL as C2 where 1 = 0 
-- Returns empty table with predefined column names
union all
select * from Test 
-- There should be exactly 2 columns, but names and data type doesn't matter

As a result, you will have a table with 2 columns [C1] and [C2]. This method is not very usefull if you have 100 columns in your table, but it works well for tables with small predefined number of columns.

Solution 2:

You'd have to do something like

declare @col1 as varchar(128)
declare @col2 as varchar(128)
declare @sq1 as varchar(8000) 

select @col1 = column_name from information_schema.columns where table_name = 'tablename'
and ordinal_position = @position

select @col2 = column_name from information_schema.columns where table_name = 'tablename'
and ordinal_position = @position2

set @sql = 'select ' + col1 ',' + col2 'from tablename' 

exec(@sql)

Solution 3:

You can use this query

select * from information_schema.columns

to get the ordinal positions of the columns. Like Michael Haren wrote, you'll have to build a dynamic query using this, either in code or in a sproc that you pass the column positions to.

FWIW, this is pure evil.

Also, deathofrats is right, you can't really do this, since you'll be building a regular query w/ column names based on position.