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.
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.
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
- Dozens more answers to tough SQL Server questions from Greg Robidoux and Jeremy Kadlec are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in February 2004