sql server select column by number

Question

Can I select specific columns by the number of the columns in SQL? Something like

SELECT columns(0), columns(3), columns(5), columns(8) FROM TABLE

Answer 1

I would highly recommend against such complicated answers.

As others already pointed out below your question, you should check out this answer instead:

Access columns of a table by index instead of name in SQL Server stored procedure

The SQL specification is not built for dynamic schema eiher in DDL or DML.

Accept it and do not use numbers for columns in the SELECT. It will be less performant, less readable and will obviously fail if you change the schema.

Answer 2

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'myTable' AND ORDINAL_POSITION = '3'

This statement returns the third column of your table

You would need to write a transact SQL statement like

DECLARE @columnname nvarchar(100), @sql nvarchar(500)

SELECT @columnname = ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = 'myTable' AND ORDINAL_POSITION = '3'

SET @sql = 'SELECT ' + @columnname + ' FROM mytable'

EXEC @sql