Reference a column by ordinal position?

[DaveBo]

Is there a way, in an SQL select statement, to refer to a column/field by other than its name?

e.g. in the Order by clause you can use "2,1" to refer to the 2nd & 1st columns.

The problem is that some imported data has huge column names, essentially long questions "What is your favorite color and the air speed velocity ..."
and I'd rather get at the data via something like
Select 1,2,3 From xxx
or
Select Field1,Field2 etc.

I know I can do this using a recordset in VB/VBA, but I'd like to use SQL.

Thanks, DaveBo

[Ecniv]

Doesn't look like it. Sorry.

Only other alternative is to create a query to sit over the table that renames the fields to managable lengths then use the query each time.

[salvelinus]

Can you rename the fields? Those aren't good names.

[Al42]

Not "can he" - he should. If this is a table of answers to questions, name the fields something like Q1, Q2, etc. In designing a database, one of the things to keep in mind is how the names of the fields are going to be used.

[salvelinus]

Yes, he should, but don't know if this is under his control. Don't know about you, but I work with a lot of things that I think could be greatly improved/are just plain dumb, but I'm stuck with them for one reason or another.

[DaveBo]

Got it using Dynamic SQL and COL_NAME() OBJECT_ID() functions

Declare @sql VarChar(4000) -- storage for dynamic SQL string
Declare @tbl VarChar(100) -- opt. var for table name
Select @tbl = 'tbl_Accounts'

-- Note the '2', '3', '4' below for the 2nd, 3rd & 4th columns.
Select @sql= 'Select
[' + COL_NAME(OBJECT_ID(@tbl), 2) + '],
[' + COL_NAME(OBJECT_ID(@tbl), 3) + '],
[' + COL_NAME(OBJECT_ID(@tbl), 4) + ']
From tbl_Accounts'
exec (@sql)

[RSINGH]

What db are you using? If it is SQL Server, then there is no problem in ordering by ordinal ie ORDER BY 2,1