We help IT Professionals succeed at work.

SQL Server: Write a query with dynamic column names

I am writing a select query using opendatasource to get data from an excel file.
There are 100s of columns in the file and i want to select only columns whose name starts with "MR"
e.g. if there are columns MR1, MR4, MR5, GG1, something5

The select query will return only MR1, MR4 and MR5

Note: The number of columns in the excel file is not constant and the count of columns starting with MR may change because of which i cannot hardcode these names.

Need help on this urgently as i am stuck.

Thanks in advance
Comment
Watch Question

Senior Developer
CERTIFIED EXPERT
Commented:
First access data from excel to store into TempTable:

SELECT * INTO TempTable FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=\\C:\Users\armand.TIMES-SG\Desktop\Saved Docs\CPFNEW2.xls;Extended Properties=Excel 8.0')...[CPFNEW2]

Then use the INFORMATION_SCHEMA.Columns to retrieve the columns that are not MR%

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TempTable' AND COLUMN_NAME NOT LIKE 'MR%%';

Then do a looping by using a CURSOR to loop the above query and get the column name to drop in the table TempTable so later we can use ('SELECT * FROM TempTable') and return only columns with MR column names.

DECLARE @col_name VARCHAR(50);
DECLARE tt_cursor CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TempTable' AND COLUMN_NAME NOT LIKE 'MR%%';
OPEN tt_cursor;
FETCH NEXT FROM tt_cursor INTO @col_name;

WHILE @@FETCH_STATUS <> 0
BEGIN
      EXEC('ALTER TABLE TempTable DROP COLUMN ' + @col_name);
      FETCH NEXT FROM tt_cursor INTO @col_name;
END

CLOSE tt_cursor;
DEALLOCATE tt_cursor;

After the above statement is finished, just run a normal SELECT:

SELECT * FROM TempTable;

You've got your final answer.
Armand GSenior Developer
CERTIFIED EXPERT

Commented:
Correction:

@@FETCH_STATUS = 0

Author

Commented:
thanks armchang ... let me try this out ...

Author

Commented:
worked with some minor modifications thanks :)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.