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 5 (of 5 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."