SELECT by ordinal position???

[nwm76]

I've got a quirky little problem where it would be very helpful to refer to columns based on their ordinal position. ie. to retrieve the 2nd column do something similar to:

SELECT 2 from <tablename> ...???

I've seen there's an ORDINAL_POSITION column in the INFORMATION_SCHEMA.COLUMNS view. Is there some way that I could make use of this?

Any suggestions much appreciated!

Cheers
Neil

 

[hmckillop]

The ordinal position can be used in order bys i.e.
SELECT * FROM MyTable Order by 2 --(second field)

In order to do what you require you would need to have dynamic sql
i.e.

declare @v_MyField varchar(100)
SELECT @v_MyField = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable' and 
ordinal_position = 2 --or whatever you need

DECLARE @sSQL varchar(1000)
SET @SQL = 'SELECT ' + @v_MyField + ' FROM MyTable'
exec (@ssQL)

"I'm living so far beyond my income that we may almost be said to be living apart