Selecting columns without specifying column name

I have a very strange scenario and would be highly grateful if you can help me out. I have one flat table of five fields, and I need to select everything from say the last three columns.

SAMPLE: 

ID CODE BRAND T1 T2
== ==== ===== == ==
1  KUU   RR4   1 2
2  KUU   R56   2 1
=====================
I need only the values of T1 and T2. The reason I need to get the last two columns is that the table is generated automatically from an Excel file, so the first three columns are static (ID, CODE, BRAND), and only last two columns are dynamic (T1, T2) and can be different next time, like (P1, P2), so I cannot do something like SELECT T1, T2 FROM sampleTable. I am totally stumped any help or advice would be great.

    Requires Free Membership to View

    Login

Unfortunately there is not an easy way of just selecting columns 3, 4 and 5 from a table without specifying the column names. If you want all columns you can easily issue the statement SELECT * FROM table, but you can't SELECT 3, 4, 5 FROM table. The SELECT syntax requires the actual column names for it to return any data.

One approach to handle your issue is to dynamically generate the SQL statement. There are two system tables that are part of each database that give you details about the tables and the columns within the tables. SYSOBJECTS is a list of all of the database objects that exist in this database. One of the object types is TABLES, so we can select the ID of the table that we are interested in from this table. The other database table is SYSCOLUMNS. This table stores a list of all of the columns that exist for a given table. So if we query both of these tables we can find the object ID for the table we want to query and then find the columns that we are interested in for the query.

For your example above you want to select columns 3, 4 and 5. The code below will find the names of these three columns and then dynamically build the SELECT statement to select these three columns. To query a different table just change the value of the @tablename parameter to the table you are trying to query.

This is a very simplistic approach without any error checking included. If you run this on a table that doesn't have a column 3, 4 or 5, the statement will still run, but no data will be returned. You also need to be aware that if you alter a table and drop a column the colid will be removed and there will be a gap in your column numbers. But for the scenario that you outlined above if the table is freshly built each time this approach should work. 

declare @tablename as varchar(128)
declare @column3 as varchar(128)
declare @column4 as varchar(128)
declare @column5 as varchar(128)
declare @statement as nvarchar(255)
select @tablename = 'suppliers'
select @column3 = sc.name 
            from sysobjects as so inner join syscolumns as sc on so.id = sc.id 
            where so.name = @tablename and sc.colid = 3
select @column4 = sc.name 
            from sysobjects as so inner join syscolumns as sc on so.id = sc.id
             where so.name = @tablename and sc.colid = 4
select @column5 = sc.name
            from sysobjects as so inner join syscolumns as sc on so.id = sc.id 
            where so.name = @tablename and sc.colid = 5
select @statement = 'select ' + @column3 + ','   + @column4 + ',' + @column5
+ ' from ' + @tablename
exec sp_executesql @statement

For More Information

This was first published in February 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top