I'm coding in SQL Server and encountered a problem.

I have Excel sheets that I have uploaded to SSMS. There is a value that is received as a name of a column (part of the header). In all sheets it's in the exact same location, but the value itself is dynamic and I can't expect it. I want to select those values.

For example:

Sheet 1

Sheet 2

And that's the format so on

I want to select the starred values that are received as headers.

Any help would be appreciated! Thanks in advance.

CodePudding user response：

Depending on how you need to select this, you can determine the name of the column from meta-data, and dynamically execute the SQL. This assumes the ordinal position of the column is always the same (3 in your case). Your situation may be different, so just know that you can fetch the column name dynamically if it helps.

declare @col1 as varchar(128)
declare @tableName as varchar(128)
declare @sql nvarchar(MAX)

SELECT @tableName = 'yourTableName'

select @col1 = column_name from information_schema.columns where table_name = @tableName
and ordinal_position = 3

SET @sql = 'SELECT '   @col1   ' FROM  '   @tableName
EXEC(@sql)

CodePudding user response：

You can get the ordinal position of the column you are looking for using this query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'YOUR_TABLE'

And then use it in a dynamic SQL statement to select the name of it using that position:

declare @COLUMN varchar(max)

select @COLUMN = column_name FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'YOUR_TABLE' and ORDINAL_POSITION = 'COLUMN_NUMBER'
 
DECLARE @sqlcmd VARCHAR(MAX)
SET @sqlcmd = 'SELECT '   QUOTENAME(@COLUMN)   ' from YOUR_TABLE';
EXEC (@sqlcmd);