不使用动态SQL T-SQL的动态别名(T-SQL Dynamic alias without us

我需要的列别名被命名为基于一个场景

declare @testing as varchar(max)
set @testing = 'choice'

select 1 as case when @testing = 'choice' then 'chose' else 'didntChoose' end

所以，如果@testing =“选择”，结果是这样的：

chose
1

其他：

didntChoose
1

是否有可能做到这一点没有动态SQL？

Answer 1:

不，你不能改变基于价值，除非你使用动态SQL别名的名称。

当你选择的列，你只能有一个名称/别名为每列。

如果你想不同的列名，那么你可以使用一些像它使用不同的SELECT语句如下：

IF @testing = 'choice'
    select 1 as 'Chose'
ELSE 
    select 1 as 'didntChoose'

或者你可以返回两个单独的列：

select 
    case when @testing = 'choice' then 1 else 0 end Chose,
    case when @testing <> 'choice' then 1 else 0 end DidNotChose

Answer 2:

下面是我写的那种即达到目的，但它不是最优雅的作品中我做过。

各种客户希望与他们的资源相关的属性显示不同的值。因此，一个通用的表存在，让每个客户分配不同的值给每个资源。

让我们首先创建的结构和填充它们。没什么特别的：

create table CustResource (
CustId int,
Attr1 varchar(50),
Attr2 varchar(50),
Attr3 varchar(50),
Attr4 varchar(50),
Attr5 varchar(50))

insert into CustResource (CustId, attr1, attr2, attr3, attr4) values (1, 'Div','Dept','Machine Type','Main Usage')

/* What just happened above is that the customer assigned display values to the first 4 attributes only */

create table PortalResource (
ResourceId int,
custId     int,
ResourceName varchar(50),
Attr1 varchar(50),
Attr2 varchar(50),
Attr3 varchar(50),
Attr4 varchar(50),
Attr5 varchar(50))


insert into PortalResource (ResourceId, CustId, ResourceName, attr1, attr2, attr3, attr4) 
   values (10,1,'abcd1234','Local Government','State Emergency Services','File Server','Production')
insert into PortalResource (ResourceId, CustId, ResourceName, attr1, attr2, attr3, attr4) 
   values (11,1,'bcde2345','Local Government','State Emergency Services','Database Server','Production')
insert into PortalResource (ResourceId, CustId, ResourceName, attr1, attr2, attr3, attr4) 
   values (12,1,'bcde2346','Local Government','Department of Education','Domain Controller','Production')

/* Notice in the above that attr5 is not populated. This is deliberate! */

/* OK, now we want to accept the customer Id (I have hard-coded it here for quick reference, but you get the point) */

declare @SQLString varchar(1000)
    , @attr1 varchar (50)
    , @attr2 varchar(50)
    , @attr3 varchar(50)
    , @attr4 varchar(50)
    , @attr5 varchar(50)
    , @CustId varchar(10)

set @CustId = 1
select @attr1 = upper(attr1)
     , @attr2 = upper(attr2)
     , @attr3 = upper(attr3)
     , @attr4 = upper(attr4 )
     , @attr5 = UPPER(attr5)
     , @CustId = convert(varchar,custId)
from CustResource where custid = @CustId

set @SQLString = 'Select '  + @CustId + 'as CustomerID'
If @attr1  is not null set @SQLString = @SQLString  + 
              ' , attr1 as '   + '"' + @attr1 + '"' 
If @attr2  is not null set @SQLString = @SQLString  + 
              ' , attr2 as ' + '"' + @attr2 + '"' 
If @attr3  is not null set @SQLString = @SQLString  + 
              ' , attr3 as ' + '"' + @attr3 + '"' 
If @attr4  is not null set @SQLString = @SQLString  + 
              ' , attr4 as ' + '"' + @attr4 + '"' 
If @attr5  is not null set @SQLString = @SQLString  + 
              ' , attr5 as ' + '"' + @attr5 + '"' 
Set @SQLString = @SQLString + ' from PortalResource where CustId = ' + @CustId 

print @SQLString

exec (@SQLString)

这个工作的魅力，但它是超级ugleeeeee !!!!

Answer 3:

我就离开这个位置http://www.sommarskog.se/dynamic_sql.html#columnalias