Select columns by ordinal position?

Question

Select columns by ordinal position?

Rootman

SSCrazy

Points: 2641
More actions
December 10, 2008 at 2:28 am

#129211

Is it possible to select columns by their ordinal position? If so , could anyone please help me with the syntax?
Many thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Rootman (12/10/2008)
Is it possible to select columns by their ordinal position? If so , could anyone please help me with the syntax?
Many thanks

SELECT *

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rootman SSCrazy Points: 2641 More actions · Answer 2

Thanks for the reply.

But I want to return a specific column, say column 2 from a table.

In other words I want to use the table as a sort of multi dimensional array. I have two integer values, and that will determin the value by column and row.

eg

Select "col2" from tblXor where rownum = 3.

I suppose I can have column names and interpret the number to a name, then use dynamic sql, but I was hoping there was a better way, using an ordinal.

Rootman SSCrazy Points: 2641 More actions · Answer 3

Just occurred to me, maybe I could use column_id in sys.columns.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Rootman (12/10/2008)
Thanks for the reply.
But I want to return a specific column, say column 2 from a table.
In other words I want to use the table as a sort of multi dimensional array. I have two integer values, and that will determin the value by column and row.
eg
Select "col2" from tblXor where rownum = 3.
I suppose I can have column names and interpret the number to a name, then use dynamic sql, but I was hoping there was a better way, using an ordinal.

The information you need for this is in syscolumns in SQL2k (which is all I've got handy right now):

select name, colorder from dbo.syscolumns WHERE [id] = (SELECT OBJECT_ID('INVOICES_Monthly','U'))

Also check out COL_NAME ( table_id , column_id ) in BOL.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 5

Don't know if you really wanted to get this involved, but check Phil Factor and Robyn Page's article out:

http://www.simple-talk.com/sql/t-sql-programming/sql-server-matrix-workbench/

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Select columns by ordinal position?

Cookies on SQLServerCentral