CODEWITHSUNDEEP
sql-servert-sql
Jesper
Jesper

Reputation: 383

SQL Server : get column data from string header

I am trying to retract a column from a table by knowing the header name of the column that I want to retrieve.

select @sName
from VPDW.dbo.Fact_Email_MW100
where VesselID = 3763 
  and (cast(UTClogTime as date) >= cast(DateAdd(day, -1, GETUTCDATE()) as date))

where 

Declare @sName nvarchar(max);

holds the string name of the column that I want to extract from my table.

Upvotes: 0

Views: 660

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Sometimes one cannot use dynamic SQL (in VIEWs, iTVFs). In such cases you can uses XML's abilities to deal with generic queries:

DECLARE @ColName VARCHAR(100)='name';

SELECT
(
    SELECT TOP 1 * 
    FROM sys.objects
    FOR XML PATH('row'),TYPE
).value('(/row/*[local-name()=sql:variable("@ColName")]/text())[1]','nvarchar(max)') AS ValueAtColumnName;

This will get one row of sys.objects and return the value for the column name.

For your query this would look like 

Declare @sName nvarchar(max);
SELECT
(
    select *
    from VPDW.dbo.Fact_Email_MW100
    where VesselID = 3763 
      and (cast(UTClogTime as date) >= cast(DateAdd(day, -1, GETUTCDATE()) as date))

).value('(/row/*[local-name()=sql:variable("@sName")]/text())[1]','nvarchar(max)') AS ValueAtColumnName;

Upvotes: 1

geco17
geco17

Reputation: 5294

The whole query needs to be dynamic in order to achieve what you're trying to do. That is, you have to build the SQL and then execute it. Consider something like the following (not tested):

declare @sName nvarchar(max)
declare @sql nvarchar(max)

set @sName = 'some_column'

set @sql = 'select ' + @sName + ' from VPDW.dbo.Fact_Email_MW100 where VesselID = 3763 and (cast(UTClogTime as date) >= cast(DateAdd(day,-1,GETUTCDATE()) as date))'
print @sql

exec sp_sqlexec  @sql

Upvotes: 3

Related Questions